This topic defines the data types supported for PostgreSQL-dialect databases.
All types except
NUMERIC 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").|
||IEEE-754 double-precision binary
floating-point format number (8
bytes) (wikipedia link).
||An alias for
||Signed eight-byte (64-bit) integer.|
||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
||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,
Canonical format for
date data type
YYYY: Four-digit year
[M]M: One or two digit month
[D]D: One or two digit day
All other open-source PostgreSQL data types are currently not supported. For example, the following common types are not supported:
TIMESTAMP WITHOUT TIME ZONE
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.
Declaring 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.|
Constructing 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