Data Types

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

For the data types defined by the ANSI standard, please refer to ANSI Data Types.

Numeric Data Types

PostgreSQL	Snowflake	Notes
BIGINT	BIGINT	​Snowflake's BIGINT is an alias for NUMBER(38,0).
BIGSERIAL	INTEGER	Snowflake's INTEGER is an alias for NUMBER(38,0). [See note on this conversion below].
DOUBLE PRECISION	DOUBLE PRECISION	Snowflake's DOUBLE PRECISION is an alias for FLOAT.
FLOAT8	FLOAT8	Snowflake's FLOAT8 is an alias for FLOAT.
INT2	SMALLINT	Snowflake's SMALLINT is an alias for NUMBER(38,0).
INT4	INTEGER	Snowflake's INTEGER is an alias for NUMBER(38,0).
INT8	INTEGER	Snowflake's INTEGER is an alias for NUMBER(38,0).
MONEY	NUMBER(38, 4)	MONEY data type is not supported in Snowflake. NUMBER(38,4) is used instead.
NUMERIC​	NUMERIC	​Snowflake's NUMERIC is an alias for NUMBER(38,0).
REAL	REAL​	​Snowflake's NUMERIC is an alias for NUMBER(38,0).
SERIAL2	SMALLINT	Snowflake's SMALLINT is an alias for NUMBER(38,0). [See note on this conversion below].
SERIAL4	SMALLINT	Snowflake's SMALLINT is an alias for NUMBER(38,0). [See note on this conversion below].
SERIAL8	INTEGER	Snowflake's INTEGER is an alias for NUMBER(38,0). [See note on this conversion below].
SERIAL	INTEGER	Snowflake's INTEGER is an alias for NUMBER(38,0). [See note on this conversion below].
SMALLINT	SMALLINT	Snowflake's SMALLINT is an alias for NUMBER(38,0).
SMALLSERIAL	SMALLINT	Snowflake's SMALLINT is an alias for NUMBER(38,0). [See note on this conversion below].

Serial data types in PostgreSQL are auto-incrementable integers. For that reason when they are present in a column definition, IDENTITY will be added as a column option for equivalent functionality.

String and Binary Data Types

PostgreSQL	Snowflake	Notes
BIT	CHARACTER	BIT data type is not supported in Snowflake. CHARACTER is used instead.
BIT VARYING	CHARACTER VARYING	BIT VARYING data type is not supported in Snowflake. CHARACTER VARYING is used instead.
BYTEA	BINARY	BYTEA data type is not supported in Snowflake. BINARY is used instead. Size limit reduced from 1GB to 8MB.
CHARACTER VARYING	CHARACTER VARYING	Snowflake's CHARACTER VARYING is an alias for VARCHAR.
TEXT	TEXT	Snowflake's TEXT is an alias for VARCHAR.

Date & Time Data Types

PostgreSQL	Snowflake	Notes
DATE	DATE	DATE accepts dates in the most common forms (YYYY-MM-DD, DD-MON-YYYY, etc.)
TIME	TIME	Storing times in the form of HH:MI:SS. Time precision can range from 0 (seconds) to 9 (nanoseconds). The default precision is 9.
TIMETZ	TIME	Time data type does not support the clause with or without out time zone in Snowflake.
TIMESTAMP	TIMESTAMP	Timestamp precision can range from 0 (seconds) to 9 (nanoseconds).
TIMESTAMPTZ	TIMESTAMPTZ	Snowflake's TIMESTAMPTZ is an alias for TIMESTAMP_TZ.
TIMESTAMP WITH TIME ZONE	TIME	Time zone not supported for time data type
TIMESTAMP WITHOUT TIME ZONE	TIME	Removed next statement, not applicable in snowflake.

Bpchar Data type

PostgreSQL	Snowflake	Notes
BPCHAR	VARCHAR	BPCHAR data type is not supported in Snowflake. VARCHAR is used instead.

For more information, please refer to MSCINF0038.

Semi-structured Data Types

PostgreSQL	Snowflake	Notes
JSON	VARIANT	VARIANT can store a value of any other type, including OBJECT and ARRAY. The maximum length of a VARIANT is 16MB.
JSONB	VARIANT	VARIANT can store a value of any other type, including OBJECT and ARRAY. The maximum length of a VARIANT is 16MB.
XML	VARIANT	VARIANT can store a value of any other type, including OBJECT and ARRAY. The maximum length of a VARIANT is 16MB.

Unsupported data types

The following data types are not supported in Snowflake. For more information, please refer to MSCEWI1028.

DATERANGE	INT4RANGE	INT8RANGE
NUMRANGE	PG_SNAPSHOT	REFCURSOR
TSRANGE	TSTZRANGE	TXID_SNAPSHOT

Pseudo types

A pseudo-type cannot be used as a column data type but can be used to declare a function's argument or result type.

Review the list for the translations of each pseudotype as shown below:

• Unknown pseudo type

Other data types

The following data types have a pending functional equivalence review. For more information, please refer to MSCEWI1073.

BOX	CIDR	CIRCLE
INET	LINE	LSEG
MACADDR	MACADDR8	PATH
PG_LSN	POINT	POLYGON
TSQUERY	TSVECTOR	UUID

Related EWIs

1. MSCEWI1028: Type is not supported.

2. MSCEWI1073: Pending Functional Equivalence Review.

3. MSCEWI1096: TIME ZONE not supported for time data type.

4. MSCINF0038: Bpchar converted to varchar.

5. MSC-PG0003: Bytea Converted To Binary.