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.
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[]
);
Input 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
|
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 eithernull
orNULL
. 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
|
string
|
ARRAY['text1', 'text2']
|
Access 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
Modify 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';
Search 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;