PostgreSQL Source and Amazon Redshift Target

The following table identifies the recommended data-type mappings for Database Ingestion and Replication configurations with a PostgreSQL source and an Amazon Redshift target:

PostgreSQL Source Data Type	Amazon Redshift Target Data Type
bigint	int8
bit varying(1)	boolean
bit varying(precision), 2 <= p <= 83886080	binary varying(size), 1 <= size <= 1024000
bit(1)	boolean
bit(precision), 2 <= p <= 83886080	binary varying(size), 1 <= size <= 1024000
boolean	boolean
box	binary varying(115)
character varying(size), 1 <= size <= 10485760	character varying(size), 4 <= size <= 65535
character(size), 1 <= size <= 10485760	character varying(size), 4 <= size <= 65535
cidr	character varying(45)
circle	binary varying(87)
date	date
daterange	character varying(29)
double precision	double precision
inet	character varying(45)
int4range	character varying(25)
int8range	character varying(43)
integer	integer
json	super
jsonb	super
line	binary varying(85)
lseg	binary varying(117)
macaddr	character varying(17)
macaddr8	character varying(23)
money	numeric(20,2)
numeric	character varying(65535)
numeric(p,s), 1 <= p <= 38, 0 <= s <= 37	numeric(p,s), 1 <= p <= 38, 0 <= s <= 37
numeric(p,s), 38 <= p <= 1000, 38 <= s <= 1000	character varying(size), 41 <= size <= 1003
numrange	character varying(65535)
path	binary varying(1024000)
point	binary varying(57)
polygon	binary varying(1024000)
real	real
smallint	smallint
time(precision) with time zone, 0 <= p <= 6	timetz
time(precision) without time zone, 0 <= p <= 6	time
timestamp(precision) with time zone, 0 <= p <= 6	timestamp with time zone
timestamp(precision) without time zone, 0 <= p <= 6	timestamp
tsrange	character varying(63)
tstzrange	character varying(75)
uuid	character(36)
xml	character varying(65535)

Limitations

•Database ingestion and replication initial load and incremental load jobs that use any of the load types can replicate data from PostgreSQL bytea, text, xml, and other large-object columns to Amazon Redshift targets if you select the Include LOBs option under Advanced on the Source page of the task wizard. LOB column data is truncated before being written to the target if it is greater in size than a byte limit that depends on the LOB type. For more information, see the "Include LOBs" description in Configure a PostgreSQL source.

Unsupported source data types

For initial load jobs, Database Ingestion and Replication does not support the following PostgreSQL data types:

•ABSTIME

•Array types

•NAME

•Object identifier types

•PG_LSN

•RELTIME

•Text search types:

- TSQUERY
- TSVECTOR

•User-defined types

For incremental load jobs, Database Ingestion and Replication does not support the following PostgreSQL data types, in addition to those not supported for initial load jobs:

•Spatial types

- Box
- Circle
- Line
- LSeg
- Path
- Point
- Polygon

•Unbounded varying types

Database ingestion and replication jobs either fail to deploy or propagate nulls for columns that have these data types.