Skip to main content

PostgreSQL Array Types

Introduction​

PostgreSQL offers a powerful feature that many other relational databases lack: the ability to store arrays as values in a column. This means you can store multiple values of the same type in a single database field, rather than having to create separate tables or use other workarounds.

Arrays in PostgreSQL can be especially useful when:

  • You need to store multiple values that logically belong together
  • You want to avoid complex join operations for simple multi-value scenarios
  • You're working with data that naturally comes in sets or lists

In this tutorial, we'll explore how to create, manipulate, and query array data types in PostgreSQL.

Creating Tables with Array Columns​

To define a column as an array, you add square brackets [] after the data type:

sql
CREATE TABLE inventory (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    sizes TEXT[],
    prices NUMERIC[]
);

This creates a table with two array columns: sizes as a text array and prices as a numeric array.

Inserting Data into Arrays​

There are several ways to insert data into array columns:

Using Curly Braces Syntax​

sql
INSERT INTO inventory (product_name, sizes, prices)
VALUES ('T-shirt', '{"Small", "Medium", "Large", "XL"}', '{19.99, 19.99, 22.99, 24.99}');

Using Array Constructor​

sql
INSERT INTO inventory (product_name, sizes, prices)
VALUES ('Jeans', ARRAY['32x30', '32x32', '34x32', '36x32'], ARRAY[49.99, 49.99, 54.99, 54.99]);

Accessing Array Elements​

PostgreSQL uses 1-based indexing for arrays (unlike many programming languages that use 0-based indexing).

Accessing a Single Element​

sql
SELECT product_name, sizes[1], prices[1] 
FROM inventory;

Output:

 product_name | sizes[1] | prices[1]
--------------+----------+-----------
 T-shirt      | Small    |     19.99
 Jeans        | 32x30    |     49.99

Accessing a Range of Elements (Slicing)​

You can also retrieve a slice of an array using the syntax array[start:end]:

sql
SELECT product_name, sizes[1:2], prices[3:4]
FROM inventory;

Output:

 product_name |   sizes[1:2]    |  prices[3:4]
--------------+-----------------+---------------
 T-shirt      | {Small,Medium}  | {22.99,24.99}
 Jeans        | {32x30,32x32}   | {54.99,54.99}

Modifying Arrays​

Updating an Entire Array​

sql
UPDATE inventory
SET sizes = ARRAY['S', 'M', 'L', 'XL', 'XXL']
WHERE product_name = 'T-shirt';

Updating a Specific Element​

sql
UPDATE inventory
SET prices[5] = 26.99
WHERE product_name = 'T-shirt';

Appending to an Array​

To add elements to an existing array, you can use the concatenation operator ||:

sql
UPDATE inventory
SET sizes = sizes || ARRAY['XXXL']
WHERE product_name = 'T-shirt';

Querying Arrays​

PostgreSQL provides powerful operators for working with arrays.

ANY Operator: Check if a Value Exists in an Array​

sql
SELECT product_name, sizes
FROM inventory
WHERE 'L' = ANY(sizes);

This returns products that have the size 'L' available.

ALL Operator: Check if All Array Elements Match a Condition​

sql
SELECT product_name, prices
FROM inventory
WHERE 50.00 > ALL(prices);

This returns products where all prices are less than $50.00.

Array Contains Operator: @>​

Check if one array contains another:

sql
SELECT product_name
FROM inventory
WHERE sizes @> ARRAY['M', 'L'];

This returns products that have both 'M' and 'L' sizes available.

Array Length​

Count the number of elements:

sql
SELECT product_name, array_length(sizes, 1) as size_count
FROM inventory;

Output:

 product_name | size_count
--------------+------------
 T-shirt      |          6
 Jeans        |          4

Array Functions​

PostgreSQL provides various functions for working with arrays:

array_append and array_prepend​

sql
-- Add to the end
SELECT array_append(ARRAY[1, 2, 3], 4);  -- Result: {1,2,3,4}

-- Add to the beginning
SELECT array_prepend(0, ARRAY[1, 2, 3]); -- Result: {0,1,2,3}

array_remove​

sql
SELECT array_remove(ARRAY[1, 2, 3, 2], 2); -- Result: {1,3}

unnest: Converting Arrays to Rows​

This is particularly useful for working with arrays in queries:

sql
SELECT product_name, unnest(sizes) as size, unnest(prices) as price
FROM inventory;

Output:

 product_name | size  | price
--------------+-------+-------
 T-shirt      | S     | 19.99
 T-shirt      | M     | 19.99
 T-shirt      | L     | 22.99
 T-shirt      | XL    | 24.99
 T-shirt      | XXL   | 26.99
 T-shirt      | XXXL  | null
 Jeans        | 32x30 | 49.99
 Jeans        | 32x32 | 49.99
 Jeans        | 34x32 | 54.99
 Jeans        | 36x32 | 54.99

Multi-dimensional Arrays​

PostgreSQL also supports multi-dimensional arrays:

sql
CREATE TABLE matrix_example (
    id SERIAL PRIMARY KEY,
    name TEXT,
    data INTEGER[][]
);

INSERT INTO matrix_example (name, data)
VALUES ('Example', '{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}');

-- Access specific element (row 2, column 3)
SELECT data[2][3] FROM matrix_example;  -- Result: 6

Practical Example: Product Tags​

Let's look at a real-world example where arrays are useful - storing product tags:

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    tags TEXT[]
);

INSERT INTO products (name, price, tags)
VALUES 
    ('Smartphone X', 699.99, ARRAY['electronics', 'smartphone', 'new release']),
    ('Laptop Pro', 1299.99, ARRAY['electronics', 'computer', 'premium']),
    ('Bluetooth Headphones', 89.99, ARRAY['electronics', 'audio', 'wireless']);

-- Find products with specific tags
SELECT name, price 
FROM products 
WHERE 'electronics' = ANY(tags) AND 'premium' = ANY(tags);

-- Count products by tag
SELECT unnest(tags) AS tag, COUNT(*) 
FROM products 
GROUP BY tag 
ORDER BY COUNT(*) DESC;

This approach is much cleaner than creating a separate tags table with a many-to-many relationship when your tagging needs are relatively simple.

When to Use (and Not Use) Arrays​

Good Use Cases​

  • Storing a small set of related values (tags, categories, attributes)
  • Simple multi-value attributes where joins would be overkill
  • Temporary storage of data that will be processed further

When to Avoid​

  • When you need to frequently update individual elements
  • When the array might grow very large (hundreds of elements)
  • When you need complex indexing on the individual elements
  • When normalization is more appropriate (e.g., when elements have their own attributes)

Summary​

PostgreSQL array types provide a powerful way to store multiple values in a single field. They offer flexibility that isn't available in many other relational databases, allowing you to:

  • Store multiple values in a single column
  • Query and filter based on array contents
  • Manipulate arrays with a rich set of operators and functions

While arrays shouldn't replace proper database normalization in many cases, they provide an elegant solution for certain data storage problems, especially when dealing with simple collections of values that logically belong together.

Additional Resources​

Exercises​

  1. Create a table to track student exam scores with columns for student name and an array of scores.
  2. Write a query to find students who have scored above 90 on any exam.
  3. Calculate the average score for each student using array functions.
  4. Create a query that returns students and their highest and lowest scores.
  5. Practice using the unnest function to convert an array of dates into individual rows.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)