Array type
PostgreSQL columns can hold arrays of any type, with operators for searching them.
In this page:
Syntax
column_name integer[]
INSERT INTO table_name (column) VALUES (ARRAY[1, 2, 3]);
SELECT column[1] FROM table_name;Array type
Declare a column as integer[] or text[] and write values as ARRAY[1,2,3] or '{1,2,3}'.
Index elements with arr[1] (arrays are 1-based), test membership with = ANY(arr), containment with @> and overlap with &&. unnest turns an array into rows and array_agg turns rows into an array. Arrays suit small, simple lists, not relationships.
Note:
PostgreSQL arrays are 1-indexed, not 0-indexed.
Example: Array type
shop=# CREATE TABLE posts (id serial PRIMARY KEY, title text, tags text[]);
shop=# INSERT INTO posts (title, tags) VALUES ('Intro', ARRAY['sql','postgres']), ('Tips', ARRAY['postgres','index']);
shop=# SELECT title FROM posts WHERE 'index' = ANY(tags);
title
-------
Tips
shop=# SELECT title, tags[1] AS first_tag FROM posts;
title | first_tag
-------+-----------
Intro | sql
Tips | postgres
shop=# SELECT unnest(tags) AS tag, count(*) FROM posts GROUP BY 1 ORDER BY 1;
tag | count
----------+-------
index | 1
postgres | 2
sql | 1⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Using arrays for data that should be a related table
- Forgetting arrays start at 1
- Expecting fast searches without a GIN index
Chapter Summary
- Any type can be an array
- Arrays are 1-based
- ANY, @> and && search arrays
- unnest and array_agg convert
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: