Fun with arrays in PostgreSQL

As you might already know, PostgreSQL comes with many, many data types. What you might not know is, that you can create arrays over all this data types quite easily. Is that important? Well, as always it depends on your requirements but there are use cases where arrays are great. As always, lets do some simple tests.

The following will create very simple table with one column, which is a one-dimensional array of integers.

postgres=# d t1
                  Table "public.t1"
 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 a      | integer[] |           |          | 

To insert data into that table you would either do it like this:

postgres=# insert into t1 (a) values ( '{1,2,3,4,5,6}' );
INSERT 0 1
postgres=# select * from t1;
       a       
---------------
 {1,2,3,4,5,6}
(1 row)

… or you can do it like this as well:

postgres=# insert into t1 (a) values ( ARRAY[1,2,3,4,5,6] );
INSERT 0 1
postgres=# select * from t1;
       a       
---------------
 {1,2,3,4,5,6}
 {1,2,3,4,5,6}
(2 rows)

Notice that I did not specify any size of the array. Although you can do that:

postgres=# create table t2 ( a int[6] );
CREATE TABLE

… the limit is not enforced by PostgreSQL:

postgres=# insert into t2 (a) values ( '{1,2,3,4,5,6,7,8}' );
INSERT 0 1
postgres=# select * from t2;
         a         
-------------------
 {1,2,3,4,5,6,7,8}
(1 row)

PostgreSQL does not limit you to one-dimensional arrays, you can well go ahead and create more dimensions:

postgres=# create table t3 ( a int[], b int[][], c int[][][] );
CREATE TABLE
postgres=# d t3
                  Table "public.t3"
 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 a      | integer[] |           |          | 
 b      | integer[] |           |          | 
 c      | integer[] |           |          | 

Although it does look like all of the columns are one-dimensional they are actually not:

postgres=# insert into t3 (a,b,c) values ( '{1,2,3}', '{{1,2,3},{1,2,3}}','{{{1,2,3},{1,2,3},{1,2,3}}}' );
INSERT 0 1
postgres=# select * from t3;
    a    |         b         |              c              
---------+-------------------+-----------------------------
 {1,2,3} | {{1,2,3},{1,2,3}} | {{{1,2,3},{1,2,3},{1,2,3}}}
(1 row)

In reality those array columns are not really one-dimensional, you can create as many dimensions as you like even when you think you created one dimension only:

postgres=# create table t4 ( a int[] );
CREATE TABLE
postgres=# insert into t4 (a) values ( '{1}' );
INSERT 0 1
postgres=# insert into t4 (a) values ( '{1,2}' );
INSERT 0 1
postgres=# insert into t4 (a) values ( '{{1,2},{1,2}}' );
INSERT 0 1
postgres=# insert into t4 (a) values ( '{{{1,2},{1,2},{1,2}}}' );
INSERT 0 1
postgres=# insert into t4 (a) values ( '{{{{1,2},{1,2},{1,2},{1,2}}}}' );
INSERT 0 1
postgres=# select * from t4;
               a               
-------------------------------
 {1}
 {1,2}
 {{1,2},{1,2}}
 {{{1,2},{1,2},{1,2}}}
 {{{{1,2},{1,2},{1,2},{1,2}}}}
(5 rows)

Now that there are some rows: how can we query that? This matches the first two rows of the table:

postgres=# select ctid,* from t4 where a[1] = 1;
 ctid  |   a   
-------+-------
 (0,1) | {1}
 (0,2) | {1,2}
(2 rows)

This matches the second row only:

postgres=# select ctid,* from t4 where a = '{1,2}';
 ctid  |   a   
-------+-------
 (0,2) | {1,2}
(1 row)

This matches row three only:

postgres=# select ctid, * from t4 where a[1:2][1:3] = '{{1,2},{1,2}}';
 ctid  |       a       
-------+---------------
 (0,3) | {{1,2},{1,2}}
(1 row)

You can even index array data types by using a GIN index:

postgres=# create index i1 ON t4 using gin (a);
CREATE INDEX
postgres=# d t4
                  Table "public.t4"
 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 a      | integer[] |           |          | 
Indexes:
    "i1" gin (a)

This does not make much sense right now is we do not have sufficient data for PostgreSQL considering the index, but a as soon as we have more data the index will be helpful:

postgres=# insert into t4 select '{{1,2},{1,2}}' from generate_series(1,1000000);
INSERT 0 1000000
postgres=# explain select ctid,* from t4 where a = '{1,2}';
                            QUERY PLAN                            
------------------------------------------------------------------
 Bitmap Heap Scan on t4  (cost=28.00..32.01 rows=1 width=51)
   Recheck Cond: (a = '{1,2}'::integer[])
   ->  Bitmap Index Scan on i1  (cost=0.00..28.00 rows=1 width=0)
         Index Cond: (a = '{1,2}'::integer[])
(4 rows)

In addition to that PostgreSQL comes with many support functions for working with arrays, e.g. to get the length of an array:

postgres=# select array_length(a,1) from t4 limit 2;
 array_length 
--------------
            1
            2

As I mentioned at the beginning of this post you can create arrays of all kinds of data types, not only integers:

postgres=# create table t5 ( a date[], b timestamp[], c text[], d point[], e boolean[] );
CREATE TABLE
postgres=# d t5
                            Table "public.t5"
 Column |             Type              | Collation | Nullable | Default 
--------+-------------------------------+-----------+----------+---------
 a      | date[]                        |           |          | 
 b      | timestamp without time zone[] |           |          | 
 c      | text[]                        |           |          | 
 d      | point[]                       |           |          | 
 e      | boolean[]                     |           |          | 

Whatever you want. You can even create arrays over user typed types:

postgres=# create type type1 as ( a int, b text );
CREATE TYPE
postgres=# create table t6 ( a type1[] );
CREATE TABLE
postgres=# d t6
                 Table "public.t6"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | type1[] |           |          | 

Quite powerful.