Working with arrays in PostgreSQL-dialect databases

Stay organized with collections Save and categorize content based on your preferences.

This page describes syntax and behavior for performing essential array management tasks for the PostgreSQL interface for Cloud Spanner. Arrays for the PostgreSQL interface share the same syntax as arrays in open-source PostgreSQL, except as described in Array type limitations. One important limitation is no support for multidimensional arrays.

Declaration of arrays

The following is an example of how to create a table that declares arrays:

CREATE TABLE lawn_care_business (
    client_name           text PRIMARY KEY,
    quarterly_fee         integer[],
    services_rendered     text[]
);

You name an array by adding square brackets ([]) to the data type name of the array elements. The statement above creates a table named lawn_care_business with two one-dimensional arrays. The first array, quarterly_fee, is an integer array. The second array, services_rendered, is a text array.

You can also specify the size of arrays when creating them:

CREATE TABLE lawn_care_business (
    client_name           text PRIMARY KEY,
    quarterly_fee         integer[4],
    services_rendered     text[3]
);

Note, however, that array size is not enforced. You can create an array with a specified size, but the size can be changed after the initial declaration. Also, although you can specify the size of an array, the length of an array returned by the array function array_length() depends on the number of elements in the array.

Array keyword constructor syntax

The PostgreSQL interface also supports the ARRAY keyword constructor syntax, which allows you to include expressions, add columns, and more.

The native PostgreSQL array constructor syntax documentation provides details on using the syntax. The PostgreSQL interface supports this functionality, with the exception of multi-dimensional arrays.

The following command creates a simple table using the ARRAY syntax:

CREATE TABLE student_id_numbers (
    id                       integer PRIMARY KEY,
    student_phone_numbers    integer ARRAY[]
);

Inputting values into array columns

A PostgreSQL interface array can only store values of one PostgreSQL type. For a list of supported PostgreSQL interface data types, see PostgreSQL data types. Nested arrays are not supported.

The standard array format for inputting values into arrays for PostgreSQL looks like this:

Data type Format PostgreSQL example
integer '{value1, value2, value3, value4}' INSERT INTO lawn_care_business
    VALUES ('Bagdan',
    '{1000, 1000, 1000, 1000}',
    '{"mowing", "fertilizing"}');

INSERT INTO lawn_care_business
    VALUES ('Esmae',
    '{2000, 2500, 2500, 2500}',
    '{"mowing", "fertilizing", "weeding"}');
string '{"text1", "text2"}'

When inputting values using this format you should be aware of the following caveats:

  • You can put double quotes around any value, even integers.
  • You must put double quotes around a string if it contains a comma or curly brace.
  • To enter a NULL value, enter either null or NULL. If you want a string that merely says NULL, enter "NULL".

You can also use the ARRAY constructor syntax to input values into an array:

Data type Format PostgreSQL example
integer ARRAY[value1, value2, value3, value4] INSERT INTO lawn_care_business
    VALUES ('Bagdan',
    ARRAY[1000, 1000, 1000, 1000],
    ARRAY['mowing', 'fertilizing']);

INSERT INTO lawn_care_business
    VALUES ('Esmae',
    ARRAY[2000, 2500, 2500, 2500],
    ARRAY['mowing', 'fertilizing', 'weeding']);
string ARRAY['text1', 'text2']

Accessing array values

You can run queries on arrays in a table. Continuing the previous example, the following query returns the names of clients who were charged a different fee between the first and second quarters of the year:

SELECT client_name FROM lawn_care_business WHERE quarterly_fee[1] <> quarterly_fee[2];

Result:

 client_name
-------------
 Esmae

PostgreSQL arrays are 1-based, meaning that for an array of size n, the first element is array[1] and the last element is at array[n].

The following query gets the third quarter fee for all clients:

SELECT quarterly_fee[3] FROM lawn_care_business;

Result:

 quarterly_fee
---------------
 1000
 2500

Modifying array values

To modify the values of an array, you must provide the values for each element in the array. For example:

UPDATE lawn_care_business SET quarterly_fee = '{2500,2500,2800,2800}'
    WHERE client_name = 'Esmae';

The following example updates the same information using ARRAY expression syntax:

UPDATE lawn_care_business SET quarterly_fee = ARRAY[2500,2500,2800,2800]
    WHERE client_name = 'Esmae';

You cannot currently update specific values of an array. This includes appending elements to an array at an unused index. For example, the following command is not supported:

UPDATE lawn_care_business SET services_rendered[4] = 'reseeding'
    WHERE client_name = 'Bagdan';

Instead, if you wish to add, remove, or the modify contents of an array, include the entire array in the query:

UPDATE lawn_care_business SET services_rendered = '{"mowing", "fertilizing", "weeding", "reseeding"}'
    WHERE client_name = 'Bagdan';

Searching for values in arrays

Each value must be checked when searching for a value in an array. If you know the size of the array, you can do this manually:

SELECT * FROM lawn_care_business WHERE quarterly_fee[1] = 1000 OR
                                       quarterly_fee[2] = 1000 OR
                                       quarterly_fee[3] = 1000 OR
                                       quarterly_fee[4] = 1000;

This is not the best method, however, because it can be too repetitive for large arrays. Also, it is not a good method if the size of the array is unknown. You can replace this search with the following query:

SELECT * FROM lawn_care_business WHERE 1000 = ANY (quarterly_fee);