This topic defines the data types supported for PostgreSQL-dialect databases.
Supported PostgreSQL data types
All types except
JSONB are valid as primary keys, foreign keys, and
FLOAT8 columns used as a key column cannot store
|Supported PostgreSQL data types||Description|
||Ordered list of zero or more elements of any non-array type. The PostgreSQL interface does not support multi-dimensional arrays. For more information, see Array type.|
||Logical Boolean (true/false).|
||Binary data ("byte array").|
||Dates ranging from 0001-01-01 to
9999-12-31. The DATE type represents a
logical calendar date, independent of
time zone. A DATE value does not represent
a specific 24-hour time period. Rather,
a given DATE value represents a different
24-hour period when interpreted in different
time zones, and may represent a shorter or
longer day during Daylight Savings Time transitions.
To represent an absolute point in time,
||IEEE-754 double-precision binary
floating-point format number (8
bytes) (Wikipedia link).
||An alias for
||Signed eight-byte (64-bit) integer.|
||Data type used for holding JSON data. It maps to the PostgreSQL JSONB data type. For more information, see Work with JSONB data.|
||Arbitrary length numeric data.
Optional precision and scale type
modifiers (for example,
||Date and time, including time zone.|
||Variable-length character string.
Optional type modifier (not applicable
Supported formats for
date data type
See the following table for the supported input formats for the
type. Note that the date interpretation is month-day-year similar to the case
DateStyle parameter is set to
MDY in open-source PostgreSQL.
|1999-01-08||ISO 8601; January 8 (recommended format)|
|January 8, 1999||Unambiguous|
|01/02/03||January 2, 2003|
|19990108||ISO 8601; January 8, 1999|
|990108||ISO 8601; January 8, 1999|
|1999.008||Year and day of year|
Supported formats for
timestamptz data type
See the following tables for the supported input formats for the
|04:05 AM||Same as 04:05; AM does not affect value|
|04:05 PM||Same as 16:05; input hour must be <= 12|
|04:05:06.789-8||ISO 8601, with time zone as UTC offset|
|04:05:06-08:00||ISO 8601, with time zone as UTC offset|
|04:05-08:00||ISO 8601, with time zone as UTC offset|
|040506-08||ISO 8601, with time zone as UTC offset|
|040506+0730||ISO 8601, with fractional-hour time zone as UTC offset|
|040506+07:30:00||UTC offset specified to seconds (not allowed in ISO 8601)|
|2003-04-12 04:05:06 America/New_York||Time zone specified by full name|
|2003-04-12 04:05:06-8||Time zone specified with -8, UTC offset for PST|
Time zone input:
|America/New_York||Full time zone name|
|-8:00:00||UTC offset for PST|
|-8:00||UTC offset for PST (ISO 8601 extended format)|
|-800||UTC offset for PST (ISO 8601 basic format)|
|-8||UTC offset for PST (ISO 8601 basic format)|
|zulu||Military abbreviation for UTC|
Unsupported formats for
timestamptz data types
Time zone abbreviations such as
z are not supported.
The following special literal values are also not supported:
For example, the following query returns an error:
Unsupported PostgreSQL data types
All other open-source PostgreSQL data types are currently not supported. The following common types are not supported:
TIMESTAMP WITHOUT TIME ZONE
Arrays in the PostgreSQL interface use the behavior and syntax described in the PostgreSQL Declaration of Array types, except for the specified Array type limitations.
For the PostgreSQL interface, an array is an ordered list of zero or more elements of non-array values. Elements in an array must share the same type.
Arrays of arrays are not allowed. Queries that would produce an array of arrays return an error. An empty array and a NULL array are two distinct values. Arrays can contain NULL elements.
Declare an array type
The following example shows how to create a table that declares an array:
CREATE TABLE students_info ( name text PRIMARY KEY, phone_numbers varchar );
Array declaration includes a name and square brackets (
) with the desired
array data type. In the previous example, the name is "phone_numbers", and
varchar denotes a varchar array for the phone contacts for students. The
previous example also adds a
text type column for student names.
||Simple array of 64-bit integers.|
||DDL syntax allows the exact size of arrays to be specified. Note, however, that declaring an array size does not enforce a size limit. Array size can be modified after declaration.|
||An alternative syntax which is more similar to the SQL standard by using the keyword ARRAY. As before, the size restriction is not enforced in any case.|
Construct an array
You can construct an array using array literals or array functions. To learn how, see Working with arrays in PostgreSQL-dialect databases.
Array limitations for PostgreSQL-dialect databases
This section lists limitations for the array data type for PostgreSQL-dialect databases, as opposed to native PostgreSQL.
The PostgreSQL interface does not support multi-dimensional arrays. For example, you cannot create the following array:
CREATE TABLE rectangle_grid ( id integer PRIMARY KEY, rectangle integer );
The PostgreSQL interface does not support using array slice