2 Ways to Insert an Array into a Table in PostgreSQL

PostgreSQL allows us to create columns that store arrays. That is, we can define the column as an array column so that we can insert arrays into the column. This enables us to work with the arrays in the columns without it being confused with plain text.

We have a couple of options for inserting arrays into columns. One is to use an array literal. Another is to use an array constructor. Examples of each of these options are below.

Insert an Array Literal

We can insert the array as a literal constant by enclosing the comma separated elements in curly braces:

INSERT INTO array_test VALUES 
    (
        '{ 1,2,3 }',
        '{ "Cat", "Dog", "Horse", "Bear", "Duck" }',
        '{ {1,2,3}, {4,5,6}, {7,8,9} }'
    );

Here, all three arrays are provided as a literal constant. These are provided in the format specified by the following syntax:

'{ val1 delim val2 delim ... }'

delim is the delimiter character for the type. This delimiter is stored in the pg_type catalog. Most of the standard data types use a comma (,), but the box type uses a semicolon (;).

Each val is either a constant of the array element type (as defined by the column’s data type), or a subarray.

Double quotes are required if the value contains commas or curly braces. Other than that, they’re optional. So in our example above, the double quotes around the string values are actually optional. We could just as easily have done this:

INSERT INTO array_test VALUES 
    (
        '{ 1,2,3 }',
        '{ Cat, Dog, Horse, Bear, Duck }',
        '{ {1,2,3}, {4,5,6}, {7,8,9} }'
    );

But as mentioned, if the string contains commas or curly braces, then we would need to surround it with double quotes.

The `ARRAY` Constructor

Another way to insert an array is with the ARRAY constructor:

INSERT INTO array_test VALUES 
    (
        ARRAY[ 41, 52, 67 ],
        ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ],
        ARRAY[ ARRAY[ 32, 78, 14 ], ARRAY[ 102, 99, 37 ], ARRAY[ 18, 65, 29 ] ]
    );

In this case we use the ARRAY keyword, followed by the elements enclosed in square brackets.

When using this method, array elements are provided as ordinary SQL constants or expressions. Therefore, string literals are single quoted instead of double quoted as they are when providing an array literal.

The ARRAY constructor builds the array based on the values we provide it. Here’s a simple example to demonstrate:

SELECT ARRAY[ 1,2,3 ];

Result:

  array  
---------
 {1,2,3}

So it’s the resulting array that’s inserted into the database column when we insert data using an array constructor.

Insert an Array Literal

The ARRAY Constructor

The `ARRAY` Constructor