Introduction to PostgreSQL Arrays

Arrays in PostgreSQL are very useful to represent lists of data without the need of extra “many-to-many” tables, but to take advantage of them, you need to know the basics; follow this article to learn them.

Fields

Arrays can be applied in all field types (including user-defined), but they are more commonly used over numbers (integers and floats) and strings (text, varchar, etc, usually as a “fake-enum”). To define them, just add a [] after its type:

create table countries (
    code text,
    name text,
    languages text[]
);

In this example, the field languages is defined as text[], meaning an “array of texts”. It is a good practice to use array fields in the plural, or other variation that convey the meaning of multiplicity.

postgres=# \d countries
              Table "public.countries"
  Column   |  Type  | Collation | Nullable | Default
-----------+--------+-----------+----------+---------
 code      | text   |           | not null |
 name      | text   |           | not null |
 languages | text[] |           |          |
Indexes:
    "countries_pkey" PRIMARY KEY, btree (code)

Arrays can be multi-dimensional, for example: integer[][] creates a 2-dimensional table of integers, but because this use not very common, I’ll not cover the details on this article.

Inserting

When you just inserting a list of values into an array, you can use the literal format with no need of casting: ’{a, b, c…}’:

insert into countries values
    ('ch', 'Switzerland', '{german, french, italian, romansh}'),
    ('it', 'Italy', '{italian, sardinian, german}'),
    ('es', 'Spain', '{spanish, catalan, galician, basque}'),
    ('py', 'Paraguay', '{spanish, guarani}');

You can also use the array[a, b, c…] constructor:

insert into countries values ('be', 'Belgium', array['dutch', 'french']);

And this is how they appear on psql:

postgres=# select * from countries;
 code |    name     |             languages
------+-------------+-----------------------------------
 ch   | Switzerland | {german,french,italian,romansh}
 it   | Italy       | {italian,sardinian,german}
 es   | Spain       | {spanish,catalan,galician,basque}
 py   | Paraguay    | {spanish,guarani}
 be   | Belgium     | {dutch,french}
(5 rows)

Selecting

Position

Arrays in PostgreSQL are 1-indexed, meaning, they start with [1] instead of [0], unlike most programing languages, for example, to get the ”first language” the syntax is language[1].

To get the ”last element” it is a bit more involved: we first need to get the index of the last value with array_upper(language, 1), and use it to as an index, like this: languages[array_upper(languages, 1)]; this 1 at the end is the dimension we are interested in (remember arrays can be multi-dimensional?).

select
    name,
    languages,
    languages[1] as first_language,
    languages[array_upper(languages, 1)] as last_language
from countries;

    name     |             languages             | first_language | last_language
-------------+-----------------------------------+----------------+---------------
 Switzerland | {german,french,italian,romansh}   | german         | romansh
 Italy       | {italian,sardinian,german}        | italian        | german
 Spain       | {spanish,catalan,galician,basque} | spanish        | basque
 Paraguay    | {spanish,guarani}                 | spanish        | guarani
 Belgium     | {dutch,french}                    | dutch          | french
(5 rows)

Contains

The symbol for the ”contains” operator is @>, and it behaves like the in operator from Python:

select * from countries where languages @> '{spanish}';

 code |   name   |             languages
------+----------+-----------------------------------
 es   | Spain    | {spanish,catalan,galician,basque}
 py   | Paraguay | {spanish,guarani}
(2 rows)

The example above matches a single value (still notated as an array), but the @> operator can work with multiple values, like this:

select * from countries where languages @> '{spanish, galician}';

 code | name  |             languages
------+-------+-----------------------------------
 es   | Spain | {spanish,catalan,galician,basque}
(1 row)

In this case both of the values need to be present.

Intersects

The symbol for ”overlap” (or ”intersects”) is &&, it behaves similar to “contains” but instead or matching both, it matches any:

select * from countries where languages && '{spanish, italian}';

 code |    name     |             languages
------+-------------+-----------------------------------
 ch   | Switzerland | {german,french,italian,romansh}
 it   | Italy       | {italian,sardinian,german}
 es   | Spain       | {spanish,catalan,galician,basque}
 py   | Paraguay    | {spanish,guarani}
(4 rows)

Unnesting

“Unnest” an array mean “split every element in its own row”, this is the basis of many interesting operations:

select code, name, unnest(languages) from countries;

 code |    name     |  unnest
------+-------------+-----------
 ch   | Switzerland | german
 ch   | Switzerland | french
 ch   | Switzerland | italian
 ch   | Switzerland | romansh
 it   | Italy       | italian
 it   | Italy       | sardinian
 it   | Italy       | german
 es   | Spain       | spanish
 es   | Spain       | catalan
 es   | Spain       | galician
 es   | Spain       | basque
 py   | Paraguay    | spanish
 py   | Paraguay    | guarani
 be   | Belgium     | dutch
 be   | Belgium     | french
(15 rows)

Combining (with deduplication and sorting)

The inverse operation of “unnest” is array_agg(). On the example below, we are unnesting in the inner select, using distinct to remove duplicates and order by to sort; after that we “re-nest” (or “aggregate”) the values back in a single array with array_agg() on the outer select:

select array_agg(lang) as all_languages
from (
    select distinct unnest(languages) as lang
    from countries
    order by lang
);

                                      all_languages
-----------------------------------------------------------------------------------------
 {basque,catalan,dutch,french,galician,german,guarani,italian,romansh,sardinian,spanish}
(1 row)

Grouping by nested values

Unnested arrays behave like in-memory tables, you can apply all other valid SQL operations over it, for example, we can group by language to give us an inverted array containing the countries that match them:

select lang, array_agg(name) as spoken_in
from (
    select name, unnest(languages) as lang
    from countries
)
group by lang order by lang;

   lang    |       spoken_in
-----------+-----------------------
 basque    | {Spain}
 catalan   | {Spain}
 dutch     | {Belgium}
 french    | {Switzerland,Belgium}
 galician  | {Spain}
 german    | {Switzerland,Italy}
 guarani   | {Paraguay}
 italian   | {Switzerland,Italy}
 romansh   | {Switzerland}
 sardinian | {Italy}
 spanish   | {Spain,Paraguay}
(11 rows)

Updating

Adding

You can add elements at the beginning of the array with array_prepend() or at the end with array_append():

update countries
set languages = array_append(languages, 'esperanto')
where code = 'be';

Changes "languages" like this: {dutch, french} ➤ {dutch, french, esperanto}.

Replacing

You can replace a single element by index, like languages[3] = ‘german’, or replace it by value with array_replace(field, from, to), as in:

update countries
set languages = array_replace(languages, 'esperanto', 'german')
where code = 'be';

Changes "languages" like this: {dutch, french, esperanto} ➤ {dutch, french, german}.

Removing

Remove elements with array_remove():

update countries
set languages = array_remove(languages, 'german')
where code = 'be';

Changes "languages" like this: {dutch, french, german} ➤ {dutch, french}.

Going deeper

Please refer to the main Array documentation and the Array Functions and Operators, they both provide more comprehensive explanations and examples, including things I didn’t cover here, like multi-dimension arrays, slices, casting, etc.

If you are looking for other compound data-types, PostgreSQL also provides hstore for simple key-value pairs and JSON for a full hierarchical JSON representation; they are both very useful and deserve their own articles!