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.

Update APT and install key utilities.
console
```
$ sudo apt install -y curl gnupg
```

Add the PostgreSQL GPG key.

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

Add the PostgreSQL APT repository.

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

Update package lists.
console
```
$ sudo apt update
```

Install the PostgreSQL client.

                            console
                            
$ sudo apt install -y postgresql-client-17

Verify the client is installed.
console
```
$ psql --version
```
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.

Connect to your PostgreSQL cluster (replace placeholders with your actual details).
console
```
$ psql -h <your-host> -p <your-port> -U <your-username> defaultdb
```
You'll see a password prompt:
```
Password for user <your-username>:
```
Enter the database password to proceed.

Create a new database.

                            sql
                            
defaultdb=> CREATE DATABASE microfinance;

Switch to the new database.
sql
```
defaultdb=> \c microfinance;
```

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

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

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.

                            sql
                            
                        
-- 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}');

                            sql
                            
                        
-- 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:

                            sql
                            
                        
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.

                            sql
                            
                        
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):

                            sql
                            
                        
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].

                            sql
                            
                        
-- 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.

                            sql
                            
                        
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.

                            sql
                            
                        
-- 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
                            
                        
UPDATE customers
SET savings_balances[3] = 4000.33
WHERE customer_id = 1;

Output.

UPDATE 1

Update the entire array.

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

Output.

UPDATE 1

Once complete, verify the updated values using:

                            sql
                            
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.

                            sql
                            
                        
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.

                            sql
                            
                        
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
                            
                        
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];

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
                            
                        
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];

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
                            
CREATE INDEX idx_phone ON customers USING GIN(phone);

Search for records where the array contains a specific value.

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

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:

                            sql
                            
                        
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.

How to Use Arrays in PostgreSQL

Prerequisites

Set Up a Sample Database

Install the PostgreSQL Client

Initialize the Database and Schema

Access Array Elements in PostgreSQL

Insert Data into Array Columns

View Inserted Records

Access Specific Array Elements

Access Array Slices

Update Array Elements

Search Array Columns in PostgreSQL

Match Specific Array Elements by Index

Search Entire Array Using `ANY`

Query Based on Value Trends in Arrays

Use GIN Index for Efficient Array Search

Unnest Arrays in PostgreSQL

Calculate Average from Array Elements

Conclusion

Comments

Products

Features

Solutions

Marketplace

Resources

Company

Tech Talks

Vultr Blogs

How to Use Arrays in PostgreSQL

Prerequisites

Set Up a Sample Database

Install the PostgreSQL Client

Initialize the Database and Schema

Access Array Elements in PostgreSQL

Insert Data into Array Columns

View Inserted Records

Access Specific Array Elements

Access Array Slices

Update Array Elements

Search Array Columns in PostgreSQL

Match Specific Array Elements by Index

Search Entire Array Using ANY

Query Based on Value Trends in Arrays

Use GIN Index for Efficient Array Search

Unnest Arrays in PostgreSQL

Calculate Average from Array Elements

Conclusion

Comments

Tech Talks

Vultr Blogs

Search Entire Array Using `ANY`