Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/spanner/docs/reference/postgresql/arrays below:

Work with arrays in PostgreSQL-dialect databases | Spanner

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.

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.

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[]
);

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:

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

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';

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;

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      |
 *--------------------+--------*/

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.

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          |
 ----------/----------- */

You can convert a subquery result into an array using the ARRAY() function.

  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.

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] |
 *-----------------*/

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.

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.

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             |
 *---------------*/

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.

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             |
 *---------------*/

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   |
 *--------------------+------*/

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.

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 NULL array elements.
• If you provide an empty string, the function inserts the separator specified in the second argument for NULL array elements.

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   |
 *------------------+--------------+---------*/

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]                               |
 *--------------------------------------------------*/

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4