PostgreSQL allows us to create arrays and store them in a database column. When we do this, we can use various array related tools to retrieve data from such arrays, as well as manipulate the data within them.

We do need to define the column as an array column though. If we don’t do this, we will likely run into trouble when we want to retrieve data from the array. For example, we can’t just store an array as the text type and then expect to be able to use subscripts to refer to its individual elements.

Fortunately, creating an array column is not much different to creating any other column. The way it works is that we specify the data type of the array elements, followed by something that defines the column as an array column. That “something” can be the ARRAY keyword, or it can be a pair of square brackets (or multiple pairs for multi dimensional arrays), or it can be both.

Using Square Brackets

Here’s a simple example that uses the square brackets syntax:

CREATE TABLE array_test(
    c1 integer[],
    c2 text[],
    c3 integer[][]
);

That code creates a table with three columns. All three columns are defined as array types, due to having square brackets appended to the data type.

• The c1 column is a single dimensional integer array, which means it accepts an array of integer values. We know it’s an array type because we appended square brackets to the data type name. And we know it’s single dimensional because it only has one pair of square brackets.
• The c2 column is a single dimensional text array.
• The c3 array is a multi dimensional integer array. It’s multi dimensional because we included more than one pair of square brackets. In this case there are two sets of square brackets, so it’s a two dimensional array.

At the time of writing, PostgreSQL 16.2 does not enforce the number of dimensions. So we could insert say, a two dimensional array into our c1 column, or a one dimensional array into the c3 column for example.

It’s possible to specify the array size, but PostgreSQL ignores this (at least as of PostgreSQL 16 at the time of writing). So we could do the following:

CREATE TABLE array_test_2(
    c1 integer[3],
    c2 text[4],
    c3 integer[3][2]
);

PostgreSQL does not enforce any such size restrictions, so we should be able to insert arrays that are a different size to what’s defined here. That said, arrays must be rectangular. Therefore, all sub-arrays at the same level must be of the same dimensions.

Using the ARRAY Keyword

Another way to define a column as a one dimensional array is with the ARRAY keyword.

Here’s an example:

CREATE TABLE array_test_3(
    c1 integer ARRAY,
    c2 text ARRAY,
    c3 integer ARRAY
);

These are all one dimensional arrays.

We have the option of specifying the array size by appending square brackets to the ARRAY keyword:

CREATE TABLE array_test_3(
    c1 integer ARRAY[3],
    c2 text ARRAY[4],
    c3 integer ARRAY[3]
);

But again, such size limits are not enforced by PostgreSQL.

Using the ARRAY keyword helps keep the code compliant with the SQL standard, but is entirely optional.

As the PostgreSQL documentation alludes to, we can only use the ARRAY keyword when defining one dimensional arrays.