Supported PostgreSQL Data Types

Here's a list of PostgreSQL data types that Oracle GoldenGate supports along with the limitations of this support.

• bigint

• bigserial

• bit(n)

• bit varying(n)

• boolean

• bytea

• char (n)

• cidr

• citext

• date

• decimal

• double precision

• Enumerated Types

• inet

• integer

• interval

• json

• jsonb

• macaddr

• macaddr8

• money

• numeric

• real

• serial

• smallint

• smallserial

• text

• time with/without timezone

• timestamp with/without timezone

• uuid

• varchar(n)

• varbit

• xml

Limitations of Support

• If columns of char, varchar, text, or bytea data types are part of a primary or unique key, then the maximum individual lengths for these columns must not exceed 8191 bytes.

• Columns of data type CITEXT that are part of the Primary Key are supported up to 8000 bytes in size. CITEXT columns that are greater than 8000 bytes and are part of the Primary Key are not supported.

• real, double, numeric, decimal: NaN input values are not supported.

• The following limitations apply to bit/varbit data types:

  • They are supported up to 4k in length. For lengths greater than 4k the data is truncated and only the lower 4k bits are captured.

  • The source bit(n) column can be applied only onto a character type column on a non-PostgreSQL target and can be applied onto a char type or a bit/varbit column on PostgreSQL target.

• The following limitations are applicable to both timestamp with time zone and timestamp without time zone:

  • The timestamp data with BC or AD tags in the data is not supported.

  • The timestamp data older than 1883-11-18 12:00:00 is not supported.

  • The timestamp data with more than 4 digits in the YEAR component is not supported.

  • Infinity/-Infinity input strings for timestamp columns are not supported.

• The following are the limitations when using interval:

  • The capture of mixed sign interval data from interval type columns is not supported. You can use DBOPTIONS ALLOWNONSTANDARDINTERVALDATA in the Extract parameter file to capture the mixed sign interval data (or any other format of interval data, which is not supported by Oracle GoldenGate) as a string (not as standard interval data).

    The following are a few examples of data that gets written to the trail file, on using the DBOPTIONS ALLOWNONSTANDARDINTERVALDATA in the Extract param file:

  • +1026-9 +0 +0:0:22.000000 is interpreted as 1026 years, 9 months, 0 days, 0 hours, 0 minutes, 22 seconds.

  • -0-0 -0 -8 is interpreted as 0 years, 0 months, 0 days, -8 hours.

  • +1-3 +0 +3:20 is interpreted as 1 year, 3 months, 0 days, 3 hours, 20 minutes.

• Replicat: If the source interval data was captured using DBOPTIONS ALLOWNONSTANDARDINTERVALDATA and written as a string to the trail, the corresponding source column is allowed to be mapped to either a char or a binary type column on the target.

• date limitations are:

  • The date data with BC or AD tags in the data is not supported.

  • Infinity/-Infinity input strings for date columns are not supported.

• Columns of text, json, xml, bytea, char (>8191), varchar (>8191) are treated as LOB columns and have the following limitations:

  • When using GETUPDATEBEFORES, the before image of LOB columns is never logged.

  • When using NOCOMPRESSUPDATES, LOB columns are logged in the after image only if they were modified.

• The support of range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.