Summary: in this tutorial, we will show you how to work with PostgreSQL array and introduce you to some handy functions for array manipulation.
Array plays an important role in PostgreSQL. Every data type has its own companion array type e.g., integer has an integer[] array type, character has character[] array type, etc. In case you define your own data type, PostgreSQL creates a corresponding array type in the background for you.
PostgreSQL allows you to define a column to be an array of any valid data type including built-in type, user-defined type or enumerated type.
The following CREATE TABLE statement creates the contacts table with the phones column is defined as an array of text.
1 2 3 4 5 | CREATE TABLE contacts ( id serial PRIMARY KEY, name VARCHAR (100), phones TEXT [] ); |
The phones column is a one-dimensional array that holds various phone numbers that a contact may have.
Insert PostgreSQL array values
The following statement inserts a new contact into the contacts table.
1 2 3 4 5 6 7 | INSERT INTO contacts (name, phones) VALUES ( 'John Doe', ARRAY [ '(408)-589-5846', '(408)-589-5555' ] ); |
We used the ARRAY constructor to construct an array and insert it into the contacts table. You can also use curly braces as follows:
1 2 3 4 5 6 7 8 9 10 | INSERT INTO contacts (name, phones) VALUES ( 'Lily Bush', '{"(408)-589-5841"}' ), ( 'William Gate', '{"(408)-589-5842","(408)-589-58423"}' ); |
The statement above inserts two rows into the contacts table. Notice that when you use curly braces, you use single quotes ' to wrap the array and double quotes " to wrap text array items.
Query array data
You use the SELECT statement to query array data as follows:
1 2 3 4 5 | SELECT name, phones FROM contacts; |
We access array elements using the subscript within square brackets []. By default, PostgreSQL uses one-based numbering for array elements. It means the first array element starts with number 1. Suppose, we want to get the contact’s name and the first phone number, we use the following query:
1 2 3 4 5 | SELECT name, phones [ 1 ] FROM contacts; |
We can use array element in the WHERE clause as the condition to filter the rows. For example, to find out who has the phone number (408)-589-58423 as the second phone number, we use the following query.
1 2 3 4 5 6 | SELECT name FROM contacts WHERE phones [ 2 ] = '(408)-589-58423'; |
Modifying PostgreSQL array
PostgreSQL allows you to update each element of an array or the whole array. The following statement updates the second phone number of William Gate.
1 2 3 4 | UPDATE contacts SET phones [ 2 ] = '(408)-589-5843' WHERE ID = 3; |
Let’s check it again.
1 2 3 4 5 6 7 8 | SELECT id, name, phones [ 2 ] FROM contacts WHERE id = 3; |
The following statement updates an array as a whole.
1 2 3 4 | UPDATE contacts SET phones = '{"(408)-589-5843"}' WHERE ID = 3; |
We verify the update using the following statement.
1 2 3 4 5 6 7 | SELECT name, phones FROM contacts WHERE id = 3; |
Search in PostgreSQL Array
Suppose, we want to know who has the phone number (408)-589-5555 regardless of position of the phone number in the phones array, we use ANY() function as follows:
1 2 3 4 5 6 7 | SELECT name, phones FROM contacts WHERE '(408)-589-5555' = ANY (phones); |
Expand Arrays
PostgreSQL provides the unnest() function to expand an array to a list of rows. For example, the following query expands all phone numbers of the phones array.
1 2 3 4 5 | SELECT name, unnest(phones) FROM contacts; |
In this tutorial, we have shown you how to work with PostgreSQL array data type and introduced you to some of the most important array operators and functions.