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 ( |
TIME | TIME | Storing times in the form of |
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. |
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
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:
Other data types
BOX | CIDR | CIRCLE |
INET | LINE | LSEG |
MACADDR | MACADDR8 | PATH |
PG_LSN | POINT | POLYGON |
TSQUERY | TSVECTOR | UUID |
Related EWIs
Last updated