PostgreSQL Source and PostgreSQL Target

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

PostgreSQL Source Data Type	PostgreSQL Target Data Type
bigint	bigint
bit varying(precision), 1 <= p <= 83886080	bytea
bit(precision), 1 <= p <= 83886080	bytea
boolean	boolean
box	bytea
character varying(size), 1 <= s <= 10485760	character varying(size), 1 <= s <= 10485760
character(size), 1 <= s <= 10485760	character varying(size), 1 <= s <= 10485760
cidr	cidr
circle	bytea
date	date
daterange	daterange
double precision	double precision
inet	inet
int4range	int4range
int8range	int8range
integer	integer
json	json
jsonb	jsonb
line	bytea
lseg	bytea
macaddr	macaddr
macaddr8	macaddr8
money	money
numeric	numeric
numeric(p,s), 1 <= p <= 1000, 0 <= s <= 1000	numeric(p,s), 1 <= p <= 1000, 0 <= s <= 1000
numrange	numrange
path	bytea
point	bytea
polygon	bytea
real	real
smallint	smallint
time(precision) with time zone, 0 <= p <= 6	time(precision) with time zone, 0 <= p <= 6
time(precision) without time zone, 0 <= p <= 6	time(precision) without time zone, 0 <= p <= 6
timestamp(precision) with time zone, 0 <= p <= 6	timestamp(precision) with time zone, 0 <= p <= 6
timestamp(precision) without time zone, 0 <= p <= 6	timestamp(precision) without time zone, 0 <= p <= 6
tsrange	tsrange
tstzrange	tstzrange
uuid	uuid
xml	xml

Limitations

•Database ingestion and replication initial load jobs can replicate data from PostgreSQL bytea, text, xml, and other large-object columns to PostgreSQL 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 and initial and 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.