The following table identifies the recommended data-type mappings for Database Ingestion and Replication configurations with a PostgreSQL source and a Microsoft SQL Server target:
PostgreSQL Source Data Type
Microsoft SQL Server Target Data Type
bigint
bigint
bit varying(1)
bit
bit varying(p), 2 <= p <= 83886080
varbinary(p), 1 <= p <= max
bit(1)
bit
bit(p), 2 <= p <= 83886080
varbinary(p), 1 <= p <= max
boolean
bit
box
varbinary(115)
character varying(p), 1 <= p <= 10485760
varchar(p), 4 <= p <= max
character(p), 1 <= p <= 10485760
varchar(p), 4 <= p <= 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
varchar(max)
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
char(p), 42 <= p <= 1003
numrange
varchar(max)
path
varbinary(max)
point
varbinary(57)
polygon
varbinary(max)
real
real
smallint
smallint
time(p) with time zone, 0 <= p <= 6
datetimeoffset(p), 0 <= p <= 6
time(p) without time zone, 0 <= p <= 6
time(p), 0 <= p <= 6
timestamp(p) with time zone, 0 <= p <= 6
datetimeoffset(p), 0 <= p <= 6
timestamp(p) without time zone, 0 <= p <= 0
datetime2(p), 0 <= p <= 6
tsrange
varchar(63)
tstzrange
varchar(75)
uuid
uniqueidentifier
xml
xml
Note: In this table, p is precision and s is size.
Limitations
•Database ingestion and replication initial load jobs can replicate data from PostgreSQL bytea, text, xml, and other large-object columns to Oracle 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.