PostgreSQL data types

This document defines the data types supported for PostgreSQL-dialect databases.

Supported PostgreSQL data types

All types except NUMERIC, FLOAT4, and JSONB are valid as primary keys, foreign keys, and secondary indexes. FLOAT8 columns used as a key column cannot store NaN values.

Supported PostgreSQL data types	Description
`array`	Ordered list of zero or more elements of any non-array type. The PostgreSQL interface does not support multi-dimensional arrays. For user-defined indexes, the lower bound must be 1 and the upper bound must be the length of the array. For more information, see Array type.
`bool` / `boolean`	Logical Boolean (true/false).
`bytea`	Binary data ("byte array").
`date`	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, use the `timestamptz` data type.
`float4` / `real`	IEEE-754 single-precision binary floating-point format number (4 bytes) (Wikipedia link). `float4` type follows PostgreSQL semantics: NaNs are greater than all non-null values. NaNs are considered equal.
`float8` / `double precision`	IEEE-754 double-precision binary floating-point format number (8 bytes) (Wikipedia link). `float8` type follows PostgreSQL semantics: NaNs are greater than all non-null values. NaNs are considered equal.
`int`	An alias for `int8`. In : open source PostgreSQL, `int` is a four-byte integer, but in PostgreSQL interface for Spanner `int` maps to `int8`, a signed eight-byte integer.
`int8` / `bigint`	Signed eight-byte (64-bit) integer.
`jsonb`	Data type used for holding JSON data. It maps to the PostgreSQL JSONB data type. For more information, see Work with JSONB data.
`numeric` / `decimal`	Arbitrary length numeric data. Optional precision and scale type modifiers (for example, `numeric(18,4)`) are supported in DQL/DML statements (e.g. `SELECT` or `INSERT`), but not in DDL statements (e.g. `CREATE` or `ALTER`). For more information, see Working with NUMERIC data `NUMERIC` follows PostgreSQL semantics: NaNs are greater than all non-null values. NaNs are considered equal.
`timestamptz`/`timestamp with time zone`	Date and time, including time zone. `timestamp with time zone` can be expressed with a UTC offset. The following timestamp literal specifies America/Los_Angeles Pacific Standard Time `2016-06-22 19:10:25-08`.
`varchar` / `text` / `character varying`	Variable-length character string. Optional type modifier (not applicable to the `text` type) specifies a column size limit in characters ( for example, `varchar(64)`). Maximum column size limit for these types is 2621440 characters.

Supported formats for `date` data type

See the following table for the supported input formats for the date data type. Note that the date interpretation is month-day-year similar to the case when the DateStyle parameter is set to MDY in open source PostgreSQL.

Example	Description
1999-01-08	ISO 8601; January 8 (recommended format)
January 8, 1999	Unambiguous
1/8/1999	January 8
1/18/1999	January 18
01/02/03	January 2, 2003
1999-Jan-08	January 8
Jan-08-1999	January 8
08-Jan-1999	January 8
99-Jan-08	Returns error
08-Jan-99	January 8
Jan-08-99	January 8
19990108	ISO 8601; January 8, 1999
990108	ISO 8601; January 8, 1999
1999.008	Year and day of year
J2451187	Julian date

Supported formats for `timestamptz` data type

See the following tables for the supported input formats for the timestamptz data type.

Time input:

Example	Description
04:05:06.789	ISO 8601
04:05:06	ISO 8601
04:05	ISO 8601
040506	ISO 8601
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:

Example	Description
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
z	Abbreviation for zulu

Unsupported formats for date and timestamptz data types

Aside from z, time zone abbreviations such as PST are not supported.

The following special literal values are also not supported: now, yesterday, today, tomorrow, epoch, -infinity and infinity.

For example, the following query returns an error:

SELECT 'today'::timestamptz;

Unsupported PostgreSQL data types

All other open source PostgreSQL data types are not supported. The following common types are not supported:

TIMESTAMP WITHOUT TIME ZONE
CHAR
INTERVAL

Array type

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 and Spanner extension to open source PostgreSQL.

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 chosen 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.

Examples

Type declarations	Description
`bigint[]`	Simple array of 64-bit integers.
`float4[] float8[]`	Array of floats, either 4 bytes or 8 bytes.
`integer[3]`	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.
`integer ARRAY[4]` `integer ARRAY`	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 open source PostgreSQL.

Multidimensional arrays

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[4][3]
);

Array slices

The PostgreSQL interface supports using the array slice syntax, as shown in the following:

SELECT (array[10, 20, 30, 40])[2:3] → {20, 30}

Array indexes

The PostgreSQL interface does not support arrays with indexes that are different from the default values. For user-defined indexes, the lower bound must be 1 and the upper bound must be the length of the array.

Spanner extension to open source PostgreSQL

Spanner extends the array data type with the VECTOR LENGTH parameter. This optional parameter sets an array to a fixed size for use in a vector search. The length must be a non-negative number and zero is allowed. You can only apply this parameter on an array that uses the float8 or double precision data types. The following example shows how to use VECTOR LENGTH in a DDL statement for CREATE TABLE:

CREATE TABLE Singers (
  id int8 NOT NULL PRIMARY KEY,
  singer_vector float[] NOT NULL VECTOR LENGTH 4
)