Michael Paquier - PostgreSQL committer

  • Home
  • Manuals
  • Presentations
  • Projects
  • Resume
  • Tags
  • About

Manipulating arrays in PostgreSQL

You liked this post or you have a comment? Contact information is available here and sponsoring is here.
09 Oct 2011
Tags: array, postgres, postgresql, unnest

Arrays can be created easily in PostgreSQL using the additional syntax [] when defining a column of a table.

CREATE TABLE aa (a int primary key, b int[]);
CREATE TABLE bb (a int primary key, b varchar(5)[]);

Arrays follow some special grammar. You can insert array data directly with ‘{data1,data2}’ format or by using things like ARRAY[data1,data2].

postgres=# INSERT INTO aa VALUES (1, '{1,2,3,4}');
INSERT 0 1
postgres=# INSERT INTO aa VALUES (2, ARRAY[1,2,3,4]);
INSERT 0 1
postgres=# select * from aa;
 a |     b     
---+-----------
 1 | {1,2,3,4}
 2 | {1,2,3,4}
(2 rows)

An array in postgres does not have any dimension restrictions. You can create arrays with multiple dimensions if desired.

postgres=# INSERT INTO aa VALUES (3, ARRAY[ARRAY[1,2],ARRAY[3,4]]);
INSERT 0 1
postgres=# INSERT INTO aa VALUES (4, ARRAY[ARRAY[1,2],ARRAY[3,4]]);
INSERT 0 1
postgres=# select * from aa WHERE a = 1;
 a |     b       
---+-----------
 1 | {1,2,3,4}
(1 row)

A special function called array_dims allows to get dimensions of an array.

postgres=# select a, array_dims(b) from aa;
 a | array_dims 
---+------------
 1 | [1:4]
 2 | [1:4]
 3 | [1:2][1:2]
 4 | [1:2][1:2]
(4 rows)

An array length can be obtained by array_length.

postgres=# select array_length(b,1) from aa where a = 1;
 array_length 
--------------
            5
(1 row)

There are another couple of useful functions like:

  • array_append, array_prepend, to add values directly to an array
  • array_cat, to assemble arrays

Here is an example.

postgres=# update aa set b = array_append(b, 5) where a = 1;
UPDATE 1
postgres=# select * from aa where a = 1;
 a |      b      
---+-------------
 1 | \{1,2,3,4,5\}
(1 row)

The contribution module int_array contains additional functions on integer arrays to sort elements.

The last function that looks useful for array manipulation are unnest and array_string. array_string returns data of a array as a string (Oh!) with a given separator.

postgres=# select array_to_string(b,';') from aa where a = 1;
 array_to_string 
-----------------
 1;2;3;4;5
(1 row)

This is particularly useful for array manipulation on application side.

unnest decomposes array into single elements. This can be used to refer to foreign tables in IN clauses for example.

postgres=# select unnest(b) from aa where a = 1;
 unnest 
--------
      1
      2
      3
      4
      5
(5 rows)
postgres=# create table cc (a int, b char(2));
CREATE TABLE
postgres=# insert into cc values (1, 'Aa'), (2, 'Bb'), (3, 'Cc'), (4, 'Dd'), (6, 'Ff');
INSERT 0 5
postgres=# select b from cc where a in (select unnest(b) from aa where a = 1);
 b  
----
 Aa
 Bb
 Cc
 Dd
(4 rows)

unnest is implemented internally since postgres 8.4. If you use an older version, you can defined it with that.

CREATE OR REPLACE FUNCTION unnest(anyarray)
  RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
    generate_series(array_lower($1,1),
                    array_upper($1,1)) i;
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

Hope you enjoyed this post.

Search

Social

Github

Mastodon

Twitter

RSS Feeds

Main

Postgres

Sponsor

Unless otherwise specified, the contents of this website are (C)Copyright Michael Paquier 2010-2023 and are licensed for use under CC BY-SA 3.0.