How to use PostgreSQL arrays

Short answer

PostgreSQL supports arrays as a first-class data type with dedicated operators, functions, and index support for efficient multi-value storage.

Steps

1. Define an array column: CREATE TABLE tags (id SERIAL PRIMARY KEY, names TEXT[]);
2. Insert arrays: INSERT INTO tags (names) VALUES (ARRAY['sql', 'database']);
3. Query with operators: SELECT * FROM tags WHERE names @> ARRAY['sql'];
4. Access elements: SELECT names[1] FROM tags;
5. Update an element: UPDATE tags SET names[2] = 'postgres' WHERE id = 1;

Tips

• Use GIN indexes on array columns for fast containment queries.
• Arrays are most useful when values are homogeneous and do not require separate table relationships.

Common issues

• Arrays do not enforce foreign key constraints on individual elements.
• Searching for a single element in a large array without an index is slow.r