Arrays, Enums, and Composite Types in PostgreSQL

Between plain scalar columns and full JSONB, Postgres has a middle tier of types that most developers underuse: arrays, enums, and composite types. Each one lets you model something a scalar can't, while keeping more structure and type safety than a JSONB blob. Knowing they exist, and when they beat both a join table and a JSON document, rounds out your data-modeling toolkit.

This is part 26 of the PostgreSQL Masterclass, following indexing JSONB.

Array columns

Any Postgres type can be an array. You declare it with [], and it stores an ordered list of values in a single column:

CREATE TABLE articles (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  tags  text[] NOT NULL DEFAULT '{}'
);

INSERT INTO articles (title, tags)
VALUES ('Postgres arrays', ARRAY['postgres', 'database', 'sql']);

Arrays shine for simple lists that belong to one row and don't need their own attributes, like tags. You query them with dedicated operators, and a GIN index makes containment fast:

CREATE INDEX articles_tags_gin ON articles USING gin (tags);

SELECT * FROM articles WHERE tags @> ARRAY['postgres'];   -- contains
SELECT * FROM articles WHERE 'postgres' = ANY(tags);      -- membership
SELECT * FROM articles WHERE tags && ARRAY['sql','nosql']; -- overlaps

The honest limit: an array is the right call when the elements are just values. The moment each element needs its own columns (a tag with a color and a created-at, say), or you need foreign keys and constraints on the elements, a proper related table beats an array. Arrays are for lists of plain values, not for dodging a join table you actually need.

Enums

An enum is a custom type with a fixed set of allowed text values. It's compact on disk (stored as a small integer internally) and self-documenting:

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'canceled');

CREATE TABLE orders (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

The database now rejects any status outside the list, and the values even sort in declaration order, which is handy. Adding a value is easy: ALTER TYPE order_status ADD VALUE 'refunded'. The friction is removing or renaming a value, which is awkward and sometimes needs recreating the type.

This connects back to the data-types article, where we compared enums against a CHECK constraint and a lookup table. The short version: use an enum for a small, stable set of values that rarely changes and carries no extra attributes. Use a CHECK constraint when the set changes more often. Use a lookup table when each status needs its own columns, like a display label or a sort weight. Enums are the most compact of the three and the least flexible.

Composite types

A composite type groups several fields into one structured value, like a struct. You can use it as a column type, giving a column internal fields:

CREATE TYPE address AS (
  street text,
  city   text,
  region text,
  postal text
);

CREATE TABLE customers (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text NOT NULL,
  billing address
);

INSERT INTO customers (name, billing)
VALUES ('Acme', ROW('1 Main St', 'Austin', 'TX', '78701'));

SELECT name, (billing).city FROM customers;

In practice, composite types as columns are used sparingly, because flat columns (billing_city, billing_postal) are usually simpler to query and index. Where composite types genuinely earn their place is as the return and parameter types of functions, letting a function return a structured row rather than loose values. If you write PL/pgSQL functions, composite types are how you pass structured data in and out cleanly.

Choosing among array, enum, JSONB, and a table

Here's how these fit together with the tools from the last two articles:

• A list of plain values owned by one row (tags, role names): an array column, GIN-indexed.
• A fixed small set of labels with no extra attributes (status, priority): an enum, or a CHECK constraint if it changes often.
• Genuinely variable, unknown-shape data (external payloads, custom fields): JSONB.
• Related entities with their own attributes or constraints (a tag with metadata, order line items): a real table with a foreign key.

The mistake to avoid is defaulting everything to JSONB because it's flexible. Arrays and enums give you flexibility with type safety and clean, indexable queries, and a proper related table gives you constraints and foreign keys. JSONB is the fallback for when the data truly has no fixed shape, not the first tool you reach for.

These middle-tier types are what let a Postgres schema stay both structured and expressive. An array for tags, an enum for status, a related table for anything with real attributes, and JSONB only for the genuinely unstructured parts: that combination models most applications cleanly without forcing everything into either rigid columns or a shapeless blob.

Next, we cover full-text search, turning that text[] and those text columns into a real search feature built into the database.