Using Postgres Array columns

How to use array columns in postgres effectively.

April 10, 2019, updated April 10, 2019

PostgreSQL supports array column types, letting you store multiple values in a single column. This is useful for tags, categories, or any list-like data that doesn’t warrant a separate join table.

Defining Array Columns

Create a table with an array column using the [] suffix on the data type:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[],
    prices NUMERIC(10, 2)[]
);

Or add an array column to an existing table:

ALTER TABLE products ADD COLUMN tags TEXT[];

Inserting Data

Use the ARRAY constructor or curly-brace literal syntax:

-- Using ARRAY constructor
INSERT INTO products (name, tags, prices)
VALUES ('Widget', ARRAY['sale', 'featured'], ARRAY[9.99, 14.99]);

-- Using literal syntax
INSERT INTO products (name, tags, prices)
VALUES ('Gadget', '{"new","popular"}', '{19.99,29.99}');

Querying Arrays

Check if an element exists

Use the ANY operator to check if a value is present in the array:

SELECT * FROM products WHERE 'sale' = ANY(tags);

Or use the @> (contains) operator:

SELECT * FROM products WHERE tags @> ARRAY['sale'];

Access by index

Array indexes in PostgreSQL are 1-based:

SELECT name, tags[1] AS first_tag FROM products;

Array length

SELECT name, array_length(tags, 1) AS tag_count FROM products;

Unnesting arrays

Convert an array into a set of rows with unnest:

SELECT name, unnest(tags) AS tag FROM products;

Updating Arrays

Replace the whole array

UPDATE products SET tags = ARRAY['clearance', 'limited'] WHERE id = 1;

Append an element

Use the array_append function:

UPDATE products SET tags = array_append(tags, 'new') WHERE id = 1;

Remove an element

Use array_remove:

UPDATE products SET tags = array_remove(tags, 'sale') WHERE id = 1;

Indexing Arrays

For fast lookups, create a GIN index on the array column:

CREATE INDEX idx_products_tags ON products USING GIN (tags);

This makes @> and ANY queries fast even on large tables.

Using Arrays with JPA / Hibernate

If you’re using Hibernate 6+, array columns are supported natively:

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @JdbcTypeCode(SqlTypes.ARRAY)
    @Column(columnDefinition = "text[]")
    private String[] tags;
}

For older Hibernate versions, you’ll need a custom UserType or use the hibernate-types library.

When to Use Arrays vs. Join Tables

Arrays are a good fit when:

The values are simple (tags, labels, small lists)
You rarely need to join against the values
You mainly query for containment (@>, ANY)

Use a traditional join table when:

You need to enforce referential integrity
You need to query from the other direction (e.g., “find all products for tag X”)
The related entity has its own attributes