PostgreSQL ENUM Types

Tip: Create ENUM Type

CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');

Gotcha: Use in Table

CREATE TABLE posts (
    status post_status NOT NULL DEFAULT 'draft'
);

Tip: Query ENUM Values

SELECT enum_range(NULL::post_status);

Returns all possible values.

Gotcha: Add Value to ENUM

ALTER TYPE post_status ADD VALUE 'pending';

Can't be done inside a transaction in older PostgreSQL versions.

Tip: Order by ENUM

ENUM values sort by declaration order, not alphabetically.

Gotcha: ENUM vs CHECK Constraint

CHECK constraints are more flexible. ENUM types are stricter and save space.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL enums are convenient for columns with a fixed set of values, but they're a migration headache. Adding a new value requires ALTER TYPE ... ADD VALUE, which can't be done in a transaction. I've seen deployment pipelines fail because the enum migration was part of a larger transaction. I now use VARCHAR with CHECK constraints instead of enums for most applications, reserving enums for truly stable value sets like days of the week.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)