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 formats for date data type

The following table shows 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.

Supported formats for timestamptz data type

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

Time input:

Time zone input:

Supported formats for interval type

The following table shows the supported input formats for the interval data type.

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

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

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 
 ) 
 

Serial types

Spanner maps serial types to identity columns with the data type bigint . Serial types are aliases but not true types so you won't see them when you serialize your schema. The following example shows how to use serial in a DDL statement for CREATE TABLE :

  ALTER 
  
 DATABASE 
  
 db 
  
 SET 
  
 spanner 
 . 
 default_sequence_kind 
  
 = 
  
 'bit_reversed_positive' 
 ; 
 CREATE 
  
 TABLE 
  
 Singers 
  
 ( 
  
 id 
  
 serial 
  
 PRIMARY 
  
 KEY 
 , 
  
 name 
  
 text 
 ); 
 

The sample output of the GetDatabaseDDL command for this schema looks like the following:

  ALTER 
  
 DATABASE 
  
 db 
  
 SET 
  
 "spanner.default_sequence_kind" 
  
 = 
  
 'bit_reversed_positive' 
 ; 
 CREATE 
  
 TABLE 
  
 singers 
  
 ( 
  
 id 
  
 bigint 
  
 GENERATED 
  
 BY 
  
 DEFAULT 
  
 AS 
  
 IDENTITY 
  
 NOT 
  
 NULL 
 , 
  
 name 
  
 character 
  
 varying 
 , 
  
 PRIMARY 
  
 KEY 
 ( 
 id 
 ) 
 ); 
 

Interval type

Interval is a query-only type, and can't be stored in a table. A database schema can't have interval columns. However, the database schema can have column expressions and views with an interval type.

Unlike open source PostgreSQL, Spanner always uses ISO 8601 date and time format for interval output.

The interval type supports the following range:

• Min: interval(months: -120000, days: -3660000, microseconds: -316224000000000000)
• Max: interval(months: 120000, days: 3660000, microseconds: 316224000000000000)