This page describes syntax and behavior for performing essential array management tasks for the PostgreSQL interface for 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 previous statement 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 lets you 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
NULLvalue, enter eithernullorNULL. 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;
Finding lengths
The array_length function returns the length of an array.
SELECT some_numbers,
array_length(some_numbers, 1) AS len
FROM
(SELECT ARRAY[0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT ARRAY[2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT ARRAY[5, 10] AS some_numbers) AS sequences;
/*--------------------+--------*
| some_numbers | len |
+--------------------+--------+
| [0, 1, 1, 2, 3, 5] | 6 |
| [2, 4, 8, 16, 32] | 5 |
| [5, 10] | 2 |
*--------------------+--------*/
Converting elements in an array to rows in a table
To convert an ARRAY into a set of rows, also known as flattening, use the
UNNEST operator. UNNEST takes an ARRAY and returns
a table with a single row for each element in the ARRAY.
Because UNNEST rearranges the order of the ARRAY elements, you might want to
restore order to the table. To do so, use the optional WITH ORDINALITY clause
to return an additional column with the index for each array element, then use
the ORDER BY clause to order the rows by their offset.
Example
SELECT *
FROM UNNEST(ARRAY['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
WITH ORDINALITY AS my_table(element, ordinality)
ORDER BY ordinality;
/-----------------------*
| element | ordinality |
+----------+------------+
| foo | 1 |
| bar | 2 |
| baz | 3 |
| qux | 4 |
| corge | 5 |
| garply | 6 |
| waldo | 7 |
| fred | 8 |
----------/----------- */
Creating arrays from subqueries
You can convert a subquery result into an array using the
ARRAY() function.
Example
SELECT some_numbers,
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS X) AS doubled
FROM (
SELECT ARRAY[0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT ARRAY[2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT ARRAY[5, 10] AS some_numbers) AS sequences;
/*--------------------+---------------------*
| some_numbers | doubled |
+--------------------+---------------------+
| [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
| [2, 4, 8, 16, 32] | [4, 8, 16, 32, 64] |
| [5, 10] | [10, 20] |
*--------------------+---------------------*/
The suquery called sequences in the example contains a column, some_numbers, of
type bigint[]. The query contains another subquery that selects each row in the
some_numbers column and uses UNNEST to return the array
as a set of rows. Then, it multiplies each value by two, and re-combines
the rows into an array using the ARRAY() operator.
Filtering arrays
The following examples use subqueries and the WHERE clause to filter an array
in the query.
SELECT
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x
WHERE x < 5) AS doubled_less_than_five
FROM (SELECT ARRAY[0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT ARRAY[2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT ARRAY[5, 10] AS some_numbers) sequences;
/*------------------------*
| doubled_less_than_five |
+------------------------+
| [0, 2, 2, 4, 6] |
| [4, 8] |
| [] |
*------------------------*/
Notice that the third row contains an empty array, because the elements in the
corresponding original row ([5, 10]) did not meet the filter requirement of
x < 5.
You can also filter arrays by using SELECT DISTINCT to return only
unique elements within an array.
SELECT ARRAY(
SELECT DISTINCT x
FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM (
SELECT ARRAY[0, 1, 1, 2, 3, 5] AS some_numbers) AS sequences;
/*-----------------*
| unique_numbers |
+-----------------+
| [0, 1, 2, 3, 5] |
*-----------------*/
Scanning arrays
To check if an array contains a specific value, use the ANY/SOME clause. To
check if an array contains a value matching a condition, use either the ALL
clause or EXISTS operator with UNNEST.
Scanning for specific values
To scan an array for a specific value, use the ANY/SOME clause.
The following example returns true if the array contains the number 2.
SELECT 2 = ANY(ARRAY[0, 1, 1, 2, 3, 5]) AS contains_value;
/*----------------*
| contains_value |
+----------------+
| true |
*----------------*/
To return the rows of a table where the array column contains a specific value,
filter the results of ANY/SOME using the WHERE clause.
Example
The following example returns the id value for the rows where the array column
contains the value 2.
SELECT id AS matching_rows
FROM (
SELECT 1 AS id, ARRAY[0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, ARRAY[2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, ARRAY[5, 10] AS some_numbers
) AS sequences
WHERE 2 = ANY(sequences.some_numbers)
ORDER BY matching_rows;
/*---------------*
| matching_rows |
+---------------+
| 1 |
| 2 |
*---------------*/
Scanning for values that satisfy a condition
To scan an array for values that match a condition, use UNNEST with subqueries
to return a table of the elements in the array, use WHERE to filter the
resulting table in the subquery, and use EXISTS to check if the filtered
table contains any rows.
Example
The following example returns the id value for rows where the array contains
values greater than 5.
SELECT id AS matching_rows
FROM (
SELECT 1 AS id, ARRAY[0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL
SELECT 2 AS id, ARRAY[2, 4, 8, 16, 32] AS some_numbers
UNION ALL
SELECT 3 AS id, ARRAY[5, 10] AS some_numbers
) AS sequences
WHERE EXISTS(SELECT * FROM UNNEST(some_numbers) AS x WHERE x > 5);
/*---------------*
| matching_rows |
+---------------+
| 2 |
| 3 |
*---------------*/
Arrays and aggregation
You can aggregate values into an array using ARRAY_AGG().
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM (
SELECT 'apple' AS fruit
UNION ALL SELECT 'pear' AS fruit
UNION ALL SELECT 'banana' AS fruit) AS fruits;
/*-----------------------*
| fruit_basket |
+-----------------------+
| [apple, pear, banana] |
*-----------------------*/
The array returned by ARRAY_AGG() is in an arbitrary order, since the order in
which the function concatenates values is not guaranteed.
You can also apply aggregate functions such as SUM() to the elements in an
array. For example, the following query returns the sum of elements for
each row of the subquery result.
SELECT some_numbers,
(SELECT SUM(x)
FROM UNNEST(s.some_numbers) AS x) AS sums
FROM (
SELECT ARRAY[0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT ARRAY[2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT ARRAY[5, 10] AS some_numbers) AS s;
/*--------------------+------*
| some_numbers | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12 |
| [2, 4, 8, 16, 32] | 62 |
| [5, 10] | 15 |
*--------------------+------*/
Converting arrays to strings
The array_to_string() function lets you convert a text array to a single
text value where the resulting value is the ordered concatenation of the array
elements.
The second argument is the separator that the function inserts between inputs to produce the output; this second argument must use the same type as the elements of the first argument.
Example
SELECT ARRAY_TO_STRING(greeting, ' ') AS greetings
FROM (
SELECT ARRAY['Hello', 'World'] AS greeting
) AS words;
/*-------------*
| greetings |
+-------------+
| Hello World |
*-------------*/
The optional third argument takes the place of NULL values in the input
array.
- If you omit this argument, then the function ignores
NULLarray elements. - If you provide an empty string, the function inserts the separator specified
in the second argument for
NULLarray elements.
Example
SELECT
ARRAY_TO_STRING(arr, '.', 'N') AS non_empty_string,
ARRAY_TO_STRING(arr, '.', '') AS empty_string,
ARRAY_TO_STRING(arr, '.') AS omitted
FROM (SELECT ARRAY['a', NULL, 'b', NULL, 'c', NULL] AS arr) AS subquery;
/*------------------+--------------+---------*
| non_empty_string | empty_string | omitted |
+------------------+--------------+---------+
| a.N.b.N.c.N | a..b..c. | a.b.c |
*------------------+--------------+---------*/
Combining arrays
In some cases, you might want to combine multiple arrays into a single array.
You can accomplish this using the || operator.
SELECT ARRAY[1, 2] || ARRAY[3, 4] || ARRAY[5, 6] AS count_to_six;
/*--------------------------------------------------*
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
*--------------------------------------------------*/