How to Use Arrays in PostgreSQL

PostgreSQL includes native support for arrays, allowing a single column to store multiple values of the same data type. This feature simplifies data modeling in cases where one-to-many relationships are minimal or unnecessary. Such as tracking phone numbers, salary history, or survey answers.

In this article, you’ll learn how to use arrays effectively in PostgreSQL. You’ll create a sample schema, insert and retrieve array data, update array elements, and explore advanced operations like slicing, searching, and unnesting arrays for analytics. These techniques help streamline multi-value data storage without relying on join-heavy table designs.

Prerequisites

Before you begin, make sure you have the following:

• A managed PostgreSQL database with the connection credentials.
• Access to a Linux instance.
• SSH access to the server.
• A non-root user with sudo privileges.

Set Up a Sample Database

To demonstrate PostgreSQL arrays, start by creating a sample database and table that stores customer data. Each customer will have multiple phone numbers and a list of savings account balances over the past three months. Ideal use cases for arrays.

Install the PostgreSQL Client

First, ensure your system can connect to the PostgreSQL database by installing the psql client.

1. Update APT and install key utilities.

   CONSOLE

   Copy

   $ sudo apt install -y curl gnupg

   Explain Code

   Powered by Vultr AgentBeta

2. Add the PostgreSQL GPG key.

   CONSOLE

   Copy

   $ curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg.gpg

   Explain Code

   Powered by Vultr AgentBeta

3. Add the PostgreSQL APT repository.

   CONSOLE

   Copy

   $ echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

   Explain Code

   Powered by Vultr AgentBeta

4. Update package lists.

   CONSOLE

   Copy

   $ sudo apt update

   Explain Code

   Powered by Vultr AgentBeta

5. Install the PostgreSQL client.

   CONSOLE

   Copy

   $ sudo apt install -y postgresql-client-17

   Explain Code

   Powered by Vultr AgentBeta

6. Verify the client is installed.

   CONSOLE

   Copy

   $ psql --version

   Explain Code

   Powered by Vultr AgentBeta

   Output.

   psql (PostgreSQL) 17.x

Initialize the Database and Schema

Connect to your PostgreSQL cluster and set up a new sample database and table to demonstrate array usage.

1. Connect to your PostgreSQL cluster (replace placeholders with your actual details).

   CONSOLE

   Copy

   $ psql -h <your-host> -p <your-port> -U <your-username> defaultdb

   Explain Code

   Powered by Vultr AgentBeta

   You'll see a password prompt:

   Password for user <your-username>:

   Enter the database password to proceed.

2. Create a new database.

   SQL

   Copy

   defaultdb=> CREATE DATABASE microfinance;

   Explain Code

   Powered by Vultr AgentBeta

3. Switch to the new database.

   SQL

   Copy

   defaultdb=> \c microfinance;

   Explain Code

   Powered by Vultr AgentBeta

4. Create the customers table with array columns for phone numbers and savings balances.

   SQL

   Copy

   microfinance=# CREATE TABLE customers (
                   customer_id SERIAL PRIMARY KEY,
                   first_name VARCHAR(50),
                   last_name VARCHAR(50),
                   phone VARCHAR[],
                   savings_balances DECIMAL(17, 2)[]
               );

   Explain Code

   Powered by Vultr AgentBeta

   The schema prepares your database for the next steps, where you’ll insert and query array data.

Access Array Elements in PostgreSQL

PostgreSQL supports array columns that store multiple values in a single field. In this section, you'll populate the array columns, retrieve individual elements and slices, and update array values using direct indexing.

Insert Data into Array Columns

PostgreSQL supports two syntaxes for inserting array values:

• Curly braces: {value1, value2, value3}
• Array constructor: ARRAY[value1, value2, value3]

Use both methods to populate the customers table with sample data.

-- Insert using curly braces
INSERT INTO customers (first_name, last_name, phone, savings_balances)
VALUES 
  ('JOHN', 'DOE', '{111423, 777849}', '{580.27, 1600.23, 4000.45}'),
  ('MARY', 'SMITH', '{444888, 999321}', '{16000.45, 32000.89, 28000.73}'),
  ('PETER', 'HENRY', '{765140, 895180}', '{1344.81, 2000.73, 3000.85}');

-- Insert using array constructor
INSERT INTO customers (first_name, last_name, phone, savings_balances)
VALUES 
  ('ANN', 'JAMES', ARRAY[789566, 345689], ARRAY[9800.23, 7800.45, 4970.23]),
  ('FRANCIS', 'ALEX', ARRAY[132563, 145289], ARRAY[6500.68, 8800.63, 3563.24]);

View Inserted Records

To verify the contents of the table:

SELECT customer_id, first_name, last_name, phone, savings_balances
FROM customers;

Output.

 customer_id | first_name | last_name |      phone      |       savings_balances
-------------+------------+-----------+-----------------+-----------------------------
           1 | JOHN       | DOE       | {111423,777849} | {580.27,1600.23,4000.45}
           2 | MARY       | SMITH     | {444888,999321} | {16000.45,32000.89,28000.73}
           3 | PETER      | HENRY     | {765140,895180} | {1344.81,2000.73,3000.85}
           4 | ANN        | JAMES     | {789566,345689} | {9800.23,7800.45,4970.23}
           5 | FRANCIS    | ALEX      | {132563,145289} | {6500.68,8800.63,3563.24}
(5 rows)

Access Specific Array Elements

PostgreSQL uses 1-based indexing to access elements. For example, phone[1] returns the first phone number.

SELECT customer_id, first_name, last_name, phone[1] AS primary_phone
FROM customers;

Output.

 customer_id | first_name | last_name | primary_phone
-------------+------------+-----------+---------------
           1 | JOHN       | DOE       | 111423
           2 | MARY       | SMITH     | 444888
           3 | PETER      | HENRY     | 765140
           4 | ANN        | JAMES     | 789566
           5 | FRANCIS    | ALEX      | 132563
(5 rows)

To get the most recent savings balance (third month):

SELECT customer_id, first_name, last_name, savings_balances[3] AS latest_balance
FROM customers;

Output.

 customer_id | first_name | last_name | latest_balance
-------------+------------+-----------+----------------
           1 | JOHN       | DOE       |        4000.45
           2 | MARY       | SMITH     |       28000.73
           3 | PETER      | HENRY     |        3000.85
           4 | ANN        | JAMES     |        4970.23
           5 | FRANCIS    | ALEX      |        3563.24
(5 rows)

Access Array Slices

PostgreSQL also supports accessing array slices using [start:end].

-- Get the first two savings balances
SELECT customer_id, first_name, last_name, savings_balances[1:2] AS first_two_months
FROM customers;

Output.

 customer_id | first_name | last_name |    first_two_months
-------------+------------+-----------+-------------------------
           1 | JOHN       | DOE       | {580.27,1600.23}
           2 | MARY       | SMITH     | {16000.45,32000.89}
           3 | PETER      | HENRY     | {1344.81,2000.73}
           4 | ANN        | JAMES     | {9800.23,7800.45}
           5 | FRANCIS    | ALEX      | {6500.68,8800.63}
(5 rows)

You can omit the lower bound to default to 1.

SELECT customer_id, first_name, last_name, savings_balances[:2] AS first_two_months
FROM customers;

Output.

 customer_id | first_name | last_name |    first_two_months
-------------+------------+-----------+-------------------------
           1 | JOHN       | DOE       | {580.27,1600.23}
           2 | MARY       | SMITH     | {16000.45,32000.89}
           3 | PETER      | HENRY     | {1344.81,2000.73}
           4 | ANN        | JAMES     | {9800.23,7800.45}
           5 | FRANCIS    | ALEX      | {6500.68,8800.63}
(5 rows)

Update Array Elements

To update an array, you can target a specific element by index or overwrite the entire array.

-- View the current values for customer 1
SELECT customer_id, first_name, last_name, savings_balances
FROM customers
WHERE customer_id = 1;

Output.

 customer_id | first_name | last_name |     savings_balances
-------------+------------+-----------+-------------------------
           1 | JOHN       | DOE       | {580.27,1600.23,4000.45}
(1 row)

• Update a single element.

  SQL

  Copy

  UPDATE customers
  SET savings_balances[3] = 4000.33
  WHERE customer_id = 1;

  Explain Code

  Powered by Vultr AgentBeta

  Output.

  UPDATE 1

• Update the entire array.

  SQL

  Copy

  UPDATE customers
  SET savings_balances = '{580.27,1600.23,4000.33}'
  WHERE customer_id = 1;

  Explain Code

  Powered by Vultr AgentBeta

  Output.

  UPDATE 1

Once complete, verify the updated values using:

SELECT savings_balances FROM customers WHERE customer_id = 1;

Output.

 savings_balances
----------------------
 {580.27,1600.23,4000.33}
(1 row)

Search Array Columns in PostgreSQL

PostgreSQL provides powerful mechanisms to search within array columns. This section demonstrates how to filter records using direct indexing, pattern matching with ANY, and advanced techniques like GIN indexing with the @> operator.

Match Specific Array Elements by Index

You can search specific positions within an array using direct index access.

SELECT customer_id, first_name, last_name, phone
FROM customers
WHERE phone[1] = '444888' OR phone[2] = '444888';

Output.

 customer_id | first_name | last_name |      phone
-------------+------------+-----------+-------------------
           2 | MARY       | SMITH     | {444888,999321}
(1 row)

Search Entire Array Using ANY

For cleaner queries that don’t depend on element positions, use the ANY keyword.

SELECT customer_id, first_name, last_name, phone
FROM customers
WHERE '444888' = ANY(phone);

Output.

 customer_id | first_name | last_name |      phone
-------------+------------+-----------+-------------------
           2 | MARY       | SMITH     | {444888,999321}
(1 row)

Query Based on Value Trends in Arrays

You can apply logical conditions across elements in the array, for example, to track growth or decline.

• Find customers whose savings increased over three months.

  SQL

  Copy

  SELECT customer_id, first_name, last_name, savings_balances
  FROM customers
  WHERE savings_balances[2] > savings_balances[1]
  AND savings_balances[3] > savings_balances[2];

  Explain Code

  Powered by Vultr AgentBeta

  Output.

  customer_id | first_name | last_name |     savings_balances
  -------------+------------+-----------+----------------------------
          1 | JOHN       | DOE       | {580.27,1600.23,4000.33}
          3 | PETER      | HENRY     | {1344.81,2000.73,3000.85}
  (2 rows)

• Find customers whose savings decreased over three months.

  SQL

  Copy

  SELECT customer_id, first_name, last_name, savings_balances
  FROM customers
  WHERE savings_balances[3] < savings_balances[2]
  AND savings_balances[2] < savings_balances[1];

  Explain Code

  Powered by Vultr AgentBeta

  Output.

  customer_id | first_name | last_name |     savings_balances
  -------------+------------+-----------+----------------------------
          4 | ANN        | JAMES     | {9800.23,7800.45,4970.23}
  (1 row)

Use GIN Index for Efficient Array Search

PostgreSQL supports full array searches using Generalized Inverted Index (GIN). This approach improves performance for large datasets.

• Create a GIN index on the phone column.

  SQL

  Copy

  CREATE INDEX idx_phone ON customers USING GIN(phone);

  Explain Code

  Powered by Vultr AgentBeta

• Search for records where the array contains a specific value.

  SQL

  Copy

  SELECT customer_id, first_name, last_name
  FROM customers
  WHERE phone @> '{"345689"}';

  Explain Code

  Powered by Vultr AgentBeta

  Output.

  customer_id | first_name | last_name
  -------------+------------+-----------
          4 | ANN        | JAMES
  (1 row)

With the GIN index in place, PostgreSQL can quickly match array values regardless of their position.

Unnest Arrays in PostgreSQL

PostgreSQL's UNNEST() function transforms array elements into individual rows, making it easier to perform aggregate operations like AVG(), SUM(), or COUNT() across array values.

Calculate Average from Array Elements

To compute the average savings across the array elements in each row, use a combination of UNNEST() and GROUP BY:

SELECT
  customer_id,
  AVG(balance) AS avg_savings
FROM
  customers,
  UNNEST(savings_balances) AS balance
GROUP BY
  customer_id
ORDER BY
  customer_id;

Output.

 customer_id |     avg_savings
-------------+-----------------------
           1 | 2060.2766666666666667
           2 | 25334.0233333333333333
           3 | 2115.4633333333333333
           4 | 7523.6366666666666667
           5 | 6288.1833333333333333
(5 rows)

• UNNEST(savings_balances) breaks each array into separate rows.
• AVG(balance) calculates the mean savings value per customer.
• The query groups results by customer_id for accurate aggregation.

This technique is powerful when analyzing metrics stored as arrays, such as time-series data, transaction histories, or sensor readings.

Conclusion

In this article, you explored how to use PostgreSQL arrays to store and manage multi-value columns within a single table. You learned how to insert and query array data, access individual elements and slices, update values, and filter results using array-aware operators and indexing. These techniques streamline schema design and enable efficient data handling for scenarios involving grouped or time-based information.