15 Arrays and ENUM

Store arrays, query their elements, and define ordered sets of ENUM values.

Two more PostgreSQL-specific types worth knowing early. Arrays let any column hold an ordered list of values of the same type — sometimes the right tool when a one-to-many relationship is so tightly scoped that a separate table is overkill (tags on a post, role names on a user). ENUM types give a column a closed set of named values, enforced by the database, and stored compactly. Both are non-standard but well-supported, and used widely in real-world Postgres apps.

What you’ll learn

Declaring and querying array columns
Array literals, indexing (1-based), and useful functions
The ANY / ALL operators on arrays
Defining and using ENUM types
When arrays/enums fit, and when a separate table is better

Example

Example 15.1 Arrays and ENUM

-- Arrays: any type can be turned into an array by appending []
DROP TABLE IF EXISTS posts_example;
CREATE TABLE posts_example (
  id   integer PRIMARY KEY,
  title text,
  tags  text[]                  -- array of text
);

INSERT INTO posts_example VALUES
  (1, 'Hello', ARRAY['intro', 'meta']),
  (2, 'SQL tips', '{tips, postgres}'),   -- shorthand literal
  (3, 'Untagged', ARRAY[]::text[]);

-- Indexing (1-based!) and length
SELECT id, title, tags[1] AS first_tag, array_length(tags, 1) AS tag_count
FROM posts_example
ORDER BY id;

-- ANY: row matches if ANY element of the array equals 'tips'
SELECT id, title FROM posts_example WHERE 'tips' = ANY(tags);

-- Array containment: @> "left contains all elements of right"
SELECT id, title FROM posts_example WHERE tags @> ARRAY['postgres'];

-- ENUM: defined type with a fixed set of allowed values
DROP TABLE IF EXISTS tickets_example;
DROP TYPE  IF EXISTS ticket_status;
CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'closed');

CREATE TABLE tickets_example (
  id     integer PRIMARY KEY,
  title  text,
  status ticket_status NOT NULL DEFAULT 'open'
);
INSERT INTO tickets_example (id, title, status) VALUES (1, 'Bug 1', 'open'), (2, 'Bug 2', 'closed');

SELECT * FROM tickets_example ORDER BY id;

Arrays in PostgreSQL are first-class: you can index them, use them in WHERE clauses, build them with ARRAY[], expand them to rows with unnest(), and query for containment with @>, <@, && (overlap). They are stored compactly inline (or in TOAST for very large ones).

The most important quirk: array indexes default to 1-based. tags[1] is the first element of the arrays here, though PostgreSQL also supports explicitly chosen lower bounds. The literal form '{a, b, c}' is a shorthand string-style literal; ARRAY['a', 'b', 'c'] is the constructor — both produce the same value.

ENUM types are excellent when you have a known small set of allowed values that should be enforced by the database ('open' | 'in_progress' | 'closed', 'small' | 'medium' | 'large'). They store as 4 bytes regardless of label length, and sort by declaration order rather than alphabetically — useful for status workflows where 'open' < 'in_progress' < 'closed' is the natural order.

Run the example

To run:

$ psql -f source/arrays-and-enum.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 3
 id |  title   | first_tag | tag_count
----+----------+-----------+-----------
  1 | Hello    | intro     |         2
  2 | SQL tips | tips      |         2
  3 | Untagged |           |
(3 rows)
 ...

Common pitfalls

1-based indexing trips up developers from every other language. tags[0] is NULL, not an error.
Modifying an ENUM (adding a value) requires ALTER TYPE; removing a value is essentially impossible without recreating the type. For sets that change often, prefer a small lookup table joined by foreign key.
Arrays look enticing for “I just need a list” — but if you ever need to query for items, join, sort, or constrain individually, a separate table with foreign keys is almost always cleaner. Use arrays when the elements are part of the row, not entities of their own.
tag = 'x' does not work on an array column — use 'x' = ANY(tag) or tag @> ARRAY['x'].

Tip

Index arrays with GIN: CREATE INDEX ON posts_example USING gin (tags); enables fast @>, <@, and && queries on the tags column. Without it, those queries scan the whole table.

Try it

Add a row with tags = ARRAY['intro','postgres','tips'] and run SELECT id, tag FROM posts_example, unnest(tags) AS tag; to expand each tag into its own row. Add a new ENUM value with ALTER TYPE ticket_status ADD VALUE 'reopened'; and insert a ticket with that status.

Downloads

SQL file: arrays-and-enum.sql · All lesson files · Setup guide