Amazon RDS for PostgreSQL with Fastah API

Amazon RDS is native PostgreSQL with PostGIS, so no special accommodations are needed.

Storing IP address / subnet

Use the inet or cidr datatype to store IPv4 and IPv6 addresses and ranges. Explicitly specify the inet_ops comparator class when indexing these types.

-- Create table
CREATE TABLE logs (
  sourceIp INET NOT NULL UNIQUE
);

-- Insert IPv4/IPv6 address
INSERT INTO logs (sourceIp) VALUES ('15.220.176.1');
INSERT INTO logs (sourceIp) VALUES ('2001:0db8:85a3::8a2e:0370:7334');

-- Create a GiST index with the inet_ops comparator class
CREATE INDEX ON logs USING GIST (sourceIp inet_ops);

Enabling PostGIS

PostGIS is not on the trusted extensions list, so you need the rds_superuser role to run.

-- Must have rds_superuser privileges
CREATE EXTENSION postgis;

Storing Coordinates

After enabling PostGIS, use ST_MakePoint to store geolocation coordinates (SRID 4326) from Fastah's locationData.lng and locationData.lat .

INSERT INTO ipLocations (sourceIp, approximateGeo)
VALUES 
  -- Melbourne, longitude = 144.94, latitude = -37.84
  ('14.1.72.1', ST_SetSRID(ST_MakePoint(144.94, -37.84), 4326)),
  -- Sydney, longitude = 151.20, latitude = -33.86
  ('2001:0db8:85a3::8a2e:0370:7334', ST_SetSRID(ST_MakePoint(151.20, -33.86), 4326));

Timezone-aware timestamps

Amazon RDS supports timezone-aware TIMESTAMPTZ objects like native PostgreSQL. Use Fastah API's locationDate.tz timezone string in the AT TIME ZONE clause.

-- Convert timezone string to timestamptz (Asia/Kathmandu example)
SELECT TO_TIMESTAMP(
    '2024-01-15 10:30:00', 
    'YYYY-MM-DD HH24:MI:SS'
) AT TIME ZONE 'Asia/Kathmandu' AS kathmandu_timestamptz;