Openflow Connector for PostgreSQL: Data mapping

This topic describes how PostgreSQL data types are mapped to Snowflake data types.

PostgreSQL to Snowflake data type mapping

The following table shows how PostgreSQL data types are mapped to Snowflake data types when replicating data.

PostgreSQL typeSnowflake typeNotes
SMALLINT / INT2INT
INTEGER / INT / INT4INT
BIGINT / INT8INT
SMALLSERIAL / SERIAL2INT
SERIAL / SERIAL4INT
BIGSERIAL / SERIAL8INT
NUMERIC / DECIMALNUMBERScale and precision are preserved within Snowflake limitations. Negative scale is converted to scale 0 with adjusted precision.
REAL / FLOAT4FLOAT
DOUBLE PRECISION / FLOAT8FLOAT
MONEYFLOAT
BOOLEAN / BOOLBOOLEAN
CHARACTER / CHAR / BPCHARTEXT
CHARACTER VARYING / VARCHARTEXTSupported by default up to 16 MB.
TEXTTEXTSupported by default up to 16 MB.
BYTEABINARYSupported by default up to 8 MB.
DATEDATEValues outside the Snowflake range are replaced with a placeholder. For more information, see Date and time values outside Snowflake limits.
TIME / TIME WITHOUT TIME ZONETIME
TIME WITH TIME ZONE / TIMETZTIMESTAMP_TZ
TIMESTAMP / TIMESTAMP WITHOUT TIME ZONETIMESTAMP_NTZValues outside the Snowflake range are replaced with a placeholder. For more information, see Date and time values outside Snowflake limits.
TIMESTAMP WITH TIME ZONE / TIMESTAMPTZTIMESTAMP_LTZValues outside the Snowflake range are replaced with a placeholder. For more information, see Date and time values outside Snowflake limits.
INTERVALTEXT
JSONVARIANTSupported by default up to 16 MB.
JSONBVARIANTSupported by default up to 16 MB.
UUIDTEXT
XMLTEXTSupported by default up to 16 MB.
BITTEXT
BIT VARYING / VARBITTEXT
POINTTEXT
LINETEXT
LSEGTEXT
BOXTEXT
PATHTEXT
POLYGONTEXT
CIRCLETEXT
CIDRTEXT
INETTEXT
MACADDRTEXT
MACADDR8TEXT
TSVECTORTEXT
TSQUERYTEXT
PG_LSNTEXT

Note

For types with default size limits (8 MB / 16 MB) in this table, it is possible to raise these limits. For details, see Oversized values.

Note

Any PostgreSQL data types not listed in this table are mapped to TEXT by default.