SnowConvert AI - PostgreSQL - Data types

Current Data types conversion for PostgreSQL to Snowflake.

Applies to

  • PostgreSQL
  • Greenplum
  • Netezza

Snowflake supports most basic SQL data types (with some restrictions) for use in columns, local variables, expressions, parameters, and any other appropriate/suitable locations.

Numeric Data Types

PostgreSQLSnowflake
INTINT
INT2SMALLINT
INT4INTEGER
INT8INTEGER
INTEGERINTEGER
BIGINTBIGINT
DECIMALDECIMAL
DOUBLE PRECISIONDOUBLE PRECISION
NUMERIC​NUMERIC
SMALLINTSMALLINT
FLOATFLOAT
FLOAT4FLOAT4
FLOAT8FLOAT8
REALREAL​
BIGSERIAL/SERIAL8

INTEGER

Note: Snowflake supports defining columns as IDENTITY, which automatically generates sequential values. This is the more concise and often preferred approach in Snowflake.

Character Types

PostgreSQLSnowflake
VARCHAR

VARCHAR

Note: VARCHAR holds Unicode UTF-8 characters. If no length is specified, the default is the maximum allowed length (16,777,216).

CHARCHAR
CHARACTER

CHARACTER

Note: Snowflake’s CHARACTER is an alias for VARCHAR.

NCHARNCHAR
BPCHAR

VARCHAR

Note: BPCHAR data type is not supported in Snowflake. VARCHAR is used instead. For more information please refer to SSC-FDM-PG0002.

CHARACTER VARYINGCHARACTER VARYING
NATIONAL CHARACTERNCHAR
NATIONAL CHARACTER VARYINGNCHAR VARYING
TEXTTEXT
NAME (Special character type)VARCHAR

Boolean Types

PostgreSQLSnowflake
BOOL/BOOLEANBOOLEAN

Binary Types

PostgreSQLSnowflake
BYTEABINARY

Bit String Types

PostgreSQLSnowflake
BITCHARACTER
BIT VARYINGCHARACTER VARYING
VARBITCHARACTER VARYING

Date & Time Data

PostgreSQLSnowflake
DATEDATE
TIMETIME
TIME WITH TIME ZONE

TIME

Note: Time zone not supported for time data type. For more information, please refer to SSC-FDM-0005.

TIME WITHOUT TIME ZONETIME
TIMESTAMPTIMESTAMP
TIMESTAMPTZTIMESTAMP_TZ
TIMESTAMP WITH TIME ZONETIMESTAMP_TZ
TIMESTAMP WITHOUT TIME ZONETIMESTAMP_NTZ
INTERVAL YEAR TO MONTH

VARCHAR

Note: Data type is not supported in Snowflake. VARCHAR is used instead. For more information please refer to SSC-EWI-0036. With the --UseIntervalDatatype preview flag, maps to INTERVAL DAY TO SECOND. See Interval Data Types.

INTERVAL DAY TO SECOND

VARCHAR

Note: Data type is not supported in Snowflake. VARCHAR is used instead. For more information please refer to SSC-EWI-0036. With the --UseIntervalDatatype preview flag, maps to INTERVAL DAY TO SECOND. See Interval Data Types.

Pseudo Types

PostgreSQLSnowflake
UNKNOWN

TEXT

Note: Data type is not supported in Snowflake. TEXT is used instead. For more information please refer to SSC-EWI-0036.

Array Types

PostgreSQLSnowflake
type []

ARRAY

Note: Strongly typed array transformed to ARRAY without type checking. For more information please refer to SSC-FDM-PG0016.

  1. SSC-FDM-PG0002: Bpchar converted to varchar.
  2. SSC-FDM-PG0003: Bytea Converted To Binary
  3. SSC-FDM-PG0014: Unknown Pseudotype transformed to Text Type
  4. SSC-FDM-0005: TIME ZONE not supported for time data type.
  5. SSC-EWI-0036: Data type converted to another data type.
  6. SSC-EWI-PG0016: Bit String Type converted to Varchar Type.
  7. SSC-FDM-PG0016: Strongly typed array transformed to ARRAY without type checking.