PostgreSQL — Arrays

Array Data Type

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[]
);

INSERT INTO products (name, tags) 
VALUES ('Phone', ARRAY['electronics', 'mobile']);

Query Arrays

-- Check membership
SELECT * FROM products WHERE 'electronics' = ANY(tags);

-- Array contains
SELECT * FROM products WHERE tags @> ARRAY['mobile'];

-- Array length
SELECT array_length(tags, 1) FROM products;

Array Functions

Function	Description
array_append	Add element
array_remove	Remove element
array_length	Get length
array_cat	Concatenate
unnest	Expand to rows

Manipulate Arrays

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

-- Remove
UPDATE products SET tags = array_remove(tags, 'old') WHERE id = 1;

-- Concatenate
UPDATE products SET tags = tags || ARRAY['extra'] WHERE id = 1;

Index Arrays

CREATE INDEX idx_tags ON products USING GIN (tags);

Mini Practice

1. Create array column
2. Query arrays
3. Manipulate arrays
4. Index arrays

Up Next

Continue with Extensions — PostgreSQL extensions.