Working with Arrays: Storing Multiple Values in PostgreSQL

Previously in this course, we explored Using JSONB for Flexible Data to handle semi-structured information. While JSONB is powerful for arbitrary data, sometimes you need a simpler, typed solution for storing lists of identical items. This lesson introduces Arrays, a native PostgreSQL feature that allows you to store multiple values within a single column.

Defining an Array Column

In our store application, we often deal with simple lists, such as product tags or alternative contact numbers. Instead of creating a separate, normalized table for every small list, we can use an array.

To define an array column, append square brackets [] to any standard data type. Here is how we might update our products table to include a list of tags:

SQL
ALTER TABLE products 
ADD COLUMN tags text[];

You can use arrays with any data type, including integer[], boolean[], or even date[]. The key principle here is that all elements in the array must be of the same type.

Querying Array Elements

Once you have data in an array, PostgreSQL provides specialized syntax to access it. Arrays in SQL are 1-indexed (the first element is at position 1).

If we wanted to select products where the first tag is 'electronics', we would use:

SQL
SELECT product_name 
FROM products 
WHERE tags[1] = 'electronics';

Often, you don't know the exact position of an item. To find rows where an array contains a specific value, use the ANY operator or the "contains" operator (@>). The @> operator is generally more efficient because it can utilize GIN (Generalized Inverted Index) indexes:

SQL
-- Find products tagged as 'sale'
SELECT product_name 
FROM products 
WHERE tags @> ARRAY['sale'];

Manipulating Arrays

Manipulating arrays involves adding, removing, or updating elements. To add a new tag to an existing product, you can concatenate arrays using the || operator:

SQL
UPDATE products 
SET tags = tags || ARRAY['featured'] 
WHERE product_id = 101;

If you need to remove an element, you can use the array_remove function:

SQL
UPDATE products 
SET tags = array_remove(tags, 'old-tag') 
WHERE product_id = 101;

Hands-on Exercise

Let’s advance our store application by tracking "discount_codes" that are valid for specific products.

1. Add a column discount_codes of type text[] to your products table.
2. Insert a product with two codes: ARRAY['SUMMER24', 'WELCOME10'].
3. Write a query to find all products that accept the 'SUMMER24' code.

Common Pitfalls

• 1-based indexing: Forgetting that arrays start at index 1 (not 0) often leads to off-by-one errors in logic.
• NULL elements: An array column can contain NULL as a value (the column itself is empty) or contain NULL elements inside the array (e.g., ARRAY['a', NULL, 'c']). Be careful with your WHERE clauses, as standard comparisons with NULL will return unknown.
• Overuse: Avoid using arrays for data that should be normalized. If you find yourself frequently joining arrays against other tables or needing to enforce complex foreign key constraints on array elements, you are likely better off using a separate table as discussed in Normalizing the Store Schema.

FAQ

Can I index an array? Yes. For the @> (contains) operator, you should create a GIN index: CREATE INDEX idx_products_tags ON products USING GIN (tags);.

What is the limit on array size? PostgreSQL arrays can hold up to 1GB of data, though in practice, large arrays will significantly degrade performance.

Can I have multi-dimensional arrays? Yes, e.g., text[][], but they are rarely needed in standard web applications and complicate query logic significantly.

Recap

Arrays provide a performant way to store lists of homogeneous data directly within a row. We learned how to define them with [], query them using the @> containment operator, and modify them with array functions like array_remove and the || operator. Remember that while arrays are convenient, they should not replace proper relational modeling for core business entities.

Up next: We will explore Full-Text Search to make your text-heavy columns searchable and performant.