PostgreSQL Source and Microsoft Azure Synapse Analytics Target

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

PostgreSQL Source Data Type	Microsoft Azure Synapse Analytics Target Data Type
bigint	bigint
bit varying(1)	varbinary(size), 1 <= size <= max
bit varying(precision), 2 <= p <= 83886080	varbinary(size), 1 <= size <= max
bit(1)	bit
bit(precision), 2 <= p <= 64000	binary(size), 1 <= size <= 8000
bit(precision), 64001 <= p <= 83886080	varbinary(max)
boolean	bit
box	varbinary(115)
character varying(size), 1 <= size <= 10485760	varchar(size), 4 <= size <= max
character(size), 1 <= size <= 10485760	varchar(size), 4 <= size <= max
cidr	varchar(45)
circle	varbinary(87)
date	date
daterange	varchar(29)
double precision	float
inet	varchar(45)
int4range	varchar(25)
int8range	varchar(43)
integer	int
json	varchar(max)
jsonb	varchar(max)
line	varbinary(85)
lseg	varbinary(117)
macaddr	varchar(17)
macaddr8	varchar(23)
money	decimal(19,2)
numeric	number
numeric(p,s), 1 <= p <= 38, 0 <= s <= 38	decimal(p,s), 1 <= p <= 38, 0 <= s <= 38
numeric(p,s), 39 <= p <= 1000, 39 <= s <= 1000	varchar(size), 42 <= s <= 1003
numrange	varchar(max)
path	varbinary(max)
point	varbinary(57)
polygon	varbinary(max)
real	real
smallint	smallint
time(precision) with time zone, 0 <= p <= 6	datetimeoffset(precision), 0 <= p <= 6
time(precision) without time zone, 0 <= p <= 6	time(precision), 0 <= p <= 6
timestamp(precision) with time zone, 0 <= p <= 6	datetimeoffset(precision), 0 <= p <= 6
timestamp(precision) without time zone, 0 <= p <= 6	datetime2(precision), 0 <= p <= 6
tsrange	varchar(63)
tstzrange	varchar(75)
uuid	uniqueidentifier
xml	varchar(max)

Limitations

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