Work 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 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:

CREATETABLElawn_care_business(client_nametextPRIMARYKEY,quarterly_feeinteger[],services_renderedtext[]);

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:

CREATETABLElawn_care_business(client_nametextPRIMARYKEY,quarterly_feeinteger[4],services_renderedtext[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:

CREATETABLEstudent_id_numbers(idintegerPRIMARYKEY,student_phone_numbersintegerARRAY[]);

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:

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:

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:

SELECTclient_nameFROMlawn_care_businessWHEREquarterly_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:

SELECTquarterly_fee[3]FROMlawn_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:

UPDATElawn_care_businessSETquarterly_fee='{2500,2500,2800,2800}'WHEREclient_name='Esmae';

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

UPDATElawn_care_businessSETquarterly_fee=ARRAY[2500,2500,2800,2800]WHEREclient_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:

UPDATElawn_care_businessSETservices_rendered[4]='reseeding'WHEREclient_name='Bagdan';

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

UPDATElawn_care_businessSETservices_rendered='{"mowing", "fertilizing", "weeding", "reseeding"}'WHEREclient_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*FROMlawn_care_businessWHEREquarterly_fee[1]=1000ORquarterly_fee[2]=1000ORquarterly_fee[3]=1000ORquarterly_fee[4]=1000;

Finding lengths

The array_length function returns the length of an array.

SELECTsome_numbers,array_length(some_numbers,1)ASlenFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECTARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECTARRAY[5,10]ASsome_numbers)ASsequences;/*--------------------+--------* | 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*FROMUNNEST(ARRAY['foo','bar','baz','qux','corge','garply','waldo','fred'])WITHORDINALITYASmy_table(element,ordinality)ORDERBYordinality;/-----------------------*|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

SELECTsome_numbers,ARRAY(SELECTx*2FROMUNNEST(some_numbers)ASX)ASdoubledFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECTARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECTARRAY[5,10]ASsome_numbers)ASsequences;/*--------------------+---------------------* | 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.

SELECTARRAY(SELECTx*2FROMUNNEST(some_numbers)ASxWHEREx < 5)ASdoubled_less_than_fiveFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECTARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECTARRAY[5,10]ASsome_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.

SELECTARRAY(SELECTDISTINCTxFROMUNNEST(some_numbers)ASx)ASunique_numbersFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbers)ASsequences;/*-----------------* | 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.

SELECT2=ANY(ARRAY[0,1,1,2,3,5])AScontains_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.

SELECTidASmatching_rowsFROM(SELECT1ASid,ARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT2ASid,ARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECT3ASid,ARRAY[5,10]ASsome_numbers)ASsequencesWHERE2=ANY(sequences.some_numbers)ORDERBYmatching_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.

SELECTidASmatching_rowsFROM(SELECT1ASid,ARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT2ASid,ARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECT3ASid,ARRAY[5,10]ASsome_numbers)ASsequencesWHEREEXISTS(SELECT*FROMUNNEST(some_numbers)ASxWHEREx > 5);/*---------------* | matching_rows | +---------------+ | 2 | | 3 | *---------------*/

Arrays and aggregation

You can aggregate values into an array using ARRAY_AGG().

SELECTARRAY_AGG(fruit)ASfruit_basketFROM(SELECT'apple'ASfruitUNIONALLSELECT'pear'ASfruitUNIONALLSELECT'banana'ASfruit)ASfruits;/*-----------------------* | 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.

SELECTsome_numbers,(SELECTSUM(x)FROMUNNEST(s.some_numbers)ASx)ASsumsFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECTARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECTARRAY[5,10]ASsome_numbers)ASs;/*--------------------+------* | 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

SELECTARRAY_TO_STRING(greeting,' ')ASgreetingsFROM(SELECTARRAY['Hello','World']ASgreeting)ASwords;/*-------------* | 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 NULL array elements.
• If you provide an empty string, the function inserts the separator specified in the second argument for NULL array elements.

Example

SELECTARRAY_TO_STRING(arr,'.','N')ASnon_empty_string,ARRAY_TO_STRING(arr,'.','')ASempty_string,ARRAY_TO_STRING(arr,'.')ASomittedFROM(SELECTARRAY['a',NULL,'b',NULL,'c',NULL]ASarr)ASsubquery;/*------------------+--------------+---------* | 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.

SELECTARRAY[1,2]||ARRAY[3,4]||ARRAY[5,6]AScount_to_six;/*--------------------------------------------------* | count_to_six | +--------------------------------------------------+ | [1, 2, 3, 4, 5, 6] | *--------------------------------------------------*/