PostgreSQL ARRAY Data Type

Summary: in this tutorial, you’ll learn how to use the PostgreSQL Array type to store arrays in the database table.

PostgreSQL ARRAY datatype overview #

In PostgreSQL, you can use the Array type to store multiple values of the same type in a single column.

For every data type, including user-defined type, PostgreSQL creates a corresponding Array data type implicitly.

For example, the INT type has the INT[] array type, VARCHAR has the VARCHAR[] array type, etc.

Here’s the syntax for defining a column with an array data type:

column_name datatype[]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, you use square brackets [] after the data type to define an array of a particular data type

For example, the following statement creates a table called products to store product information:

CREATE TABLE products (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  features TEXT[]
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In the products table, the features column has a data type of a one-dimensional array that can store multiple text values.

An array can be one-dimensional or multi-dimensional. The number of bracket squares determines the number of dimensions of an array.

For example, the following shows how to define a column with two dimensions:

column_name datatype [][]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Inserting Data into the Array Column #

To construct a value of an array, you can use either curly braces {} or the ARRAY constructor.

The following statement uses an ARRAY constructor inserts a new row into the products table :

INSERT INTO
  products (name, price, features)
VALUES
  (
    'Smartphone X1',
    699.99,
    ARRAY['5G', 'OLED Display', '512GB Storage']
  )
RETURNING
  name,
  price,
  features;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     name      | price  |              features
---------------+--------+-------------------------------------
 Smartphone X1 | 699.99 | {5G,"OLED Display","512GB Storage"}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following INSERT statement uses curly braces to insert a new product into the products table:

INSERT INTO
  products (name, price, features)
VALUES
  (
    'Laptop Pro 15',
    1299.99,
    '{"Touchscreen", "32GB RAM", "1024GB SSD"}'
  )
RETURNING
  name,
  price,
  features;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     name      |  price  |               features
---------------+---------+---------------------------------------
 Laptop Pro 15 | 1299.99 | {Touchscreen,"32GB RAM","1024GB SSD"}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Querying array data #

PostgreSQL arrays use 1-based indexing i.e., the first arary element is 1, the second array element is 2, etc.

The following query retrieves the product name and the first feature from the products table:

SELECT
  name,
  features[1] AS main_feature
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     name      | main_feature
---------------+--------------
 Smartphone X1 | 5G
 Laptop Pro 15 | TouchscreenCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the query retrieves the first feature of each product in the products table.

The following query retrieves the full array of features:

SELECT
  name,
  features
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     name      |               features
---------------+---------------------------------------
 Smartphone X1 | {5G,"OLED Display","512GB Storage"}
 Laptop Pro 15 | {Touchscreen,"32GB RAM","1024GB SSD"}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Modifying array elements #

PostgreSQL allows you to update a specific element in an array or replace the entire array.

For example, the following UPDATE statement modifies the second element in an array:

UPDATE products
SET
  features[2] = 'Super Retina Display'
WHERE
  id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

And the following statement replaces the entire array:

UPDATE products
SET
  features = ARRAY[
    'OLED Display',
    '256GB Storage',
    'Water Resistant'
  ]
WHERE
  id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Searching within an array #

You use the ANY operator to check if an element exists in an array. For example, the following statement retrieves the products that have 5G as one of their features:

SELECT
  name, features
FROM
  products
WHERE
  '5G' = ANY (features);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     name      |                  features
---------------+---------------------------------------------
 Smartphone X1 | {5G,"Super Retina Display","512GB Storage"}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Expanding arrays into rows #

To expand an arrays into a set of rows, you use the UNNEST function:

SELECT
  name,
  UNNEST(features) AS feature
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     name      |       feature
---------------+----------------------
 Smartphone X1 | 5G
 Smartphone X1 | Super Retina Display
 Smartphone X1 | 512GB Storage
 Laptop Pro 15 | OLED Display
 Laptop Pro 15 | 256GB Storage
 Laptop Pro 15 | Water ResistantCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You use the UNNEST function to treat array elements as individual rows.

Counting array elements #

The ARRAY_LENGTH function returns the number of elements in an array:

ARRAY_LENGTH(array, dimension)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following query uses the ARRAY_LENGTH function to return the number of features for each product:

SELECT
  name,
  array_length(features, 1) AS feature_count
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The argument 1 indicates one-dimensional array.

Output:

     name      | feature_count
---------------+---------------
 Smartphone X1 |             3
 Laptop Pro 15 |             3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Adding a new element to an array #

The ARRAY_APPEND appends an element to the end of an array.

For example, the following statement uses the ARRAY_APPEND function to add a feature to the product id 1:

UPDATE products
SET
  features = ARRAY_APPEND(features, 'Fingerprint')
WHERE
  id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The following query verifies the update:

SELECT
  name,
  features
FROM
  products
WHERE
  id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     name      |                        features
---------------+---------------------------------------------------------
 Smartphone X1 | {5G,"Super Retina Display","512GB Storage",Fingerprint}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Removing an element from an array #

To delete all occurrences of an element from an array, you use the ARRAY_REMOVE function:

ARRAY_REMOVE(array, element)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement removes the Touchscreen feature from the product id 2:

UPDATE products
SET
  features = ARRAY_REMOVE(features, 'Touchscreen')
WHERE
  id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The following query checks the update:

SELECT
  name,
  features
FROM
  products
WHERE
  id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

     name      |                      features
---------------+----------------------------------------------------
 Laptop Pro 15 | {"OLED Display","256GB Storage","Water Resistant"}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Indexing array columns #

If performance is critical, consider using GIN indexes to speed up searches in arrays.

For example, the following statement creates an index for the data in the features column:

CREATE INDEX idx_features ON products USING GIN (features);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Typically, you use arrays for small lists like tags, attributes, etc.

If you need to query data frequently, consider storing array data in a separate table and creating a relationship with the main table.

Summary #

An array is a collection of elements with the same data type.
Use arrays to store multiple values in a column.

Was this tutorial helpful ?