Amazon RDS for PostgreSQL

Amazon RDS for PostgreSQL supports all Fastah-relevant data types natively: inet for IP addresses, PostGIS for geolocation points, and timestamptz for timezone-aware timestamps.

Store IP addresses and subnets

Use inet or cidr to store IPv4 and IPv6 addresses. Specify inet_ops when creating a GiST index:

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

-- Works with both IPv4 and IPv6
INSERT INTO logs (sourceIp) VALUES ('15.220.176.1');
INSERT INTO logs (sourceIp) VALUES ('2001:0db8:85a3::8a2e:0370:7334');

-- Use inet_ops for range queries
CREATE INDEX ON logs USING GIST (sourceIp inet_ops);

Enable PostGIS and store coordinates

PostGIS isn't a trusted extension, so you need rds_superuser to enable it:

-- Requires rds_superuser role
CREATE EXTENSION postgis;

Use ST_MakePoint(lng, lat) to store Fastah's locationData coordinates with SRID 4326:

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

Store timezone-aware timestamps

Use TIMESTAMPTZ with Fastah's locationData.tz timezone string:

-- Use locationData.tz from Fastah response
SELECT TO_TIMESTAMP('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS')
  AT TIME ZONE 'Asia/Kathmandu' AS localTime;