🏷

PostgreSQL Enum Type Functions

Complete reference for PostgreSQL enum functions including enum_first(), enum_last(), and enum_range() for iterating over values of custom enum types. Covers enum creation, ordering semantics, comparison behaviour, and altering enums. Updated for PostgreSQL 16.

3 functions

Jump to function

enum_first enum_last enum_range

What are PostgreSQL Enum Type Functions?

PostgreSQL enum functions allow programmatic iteration over custom enum types defined with CREATE TYPE ... AS ENUM. enum_first() returns the first value in an enum, enum_last() returns the last, and enum_range() returns all values as an ordered array. Enums in PostgreSQL are ordered by their definition order and support full comparison operators (=, <, >, BETWEEN), making them suitable for status columns and ordered categories.

enum_first

PG 8.3+→ anyenum

Returns the first value of the enum type. Pass any value of the enum type (or NULL::enum_type) to specify which enum.

DeveloperDBA

Signature

enum_first ( anyenum ) → anyenum

Parameters

Parameter	Type	Description
anyenum	anyenum	Any value of the enum type (the value itself is ignored — only the type matters)

Examples

sql

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
SELECT enum_first(NULL::mood);

→sad

sql

SELECT enum_first(status) FROM orders LIMIT 1;

→First value of the orders.status enum type

sql

SELECT col, enum_first(NULL::priority_level) AS lowest_priority
FROM config;

→Adds lowest_priority column showing the first enum value

sql

SELECT count(*) FROM tasks
WHERE priority = enum_first(NULL::task_priority);

→Count of tasks at the lowest priority level

⚠Anti-Pattern

Use `enum_first(NULL::enum_type)` and `enum_last(NULL::enum_type)` to get the boundary values of an enum without hardcoding them. Useful in validation functions that should adapt when the enum is extended.

example

SELECT * FROM tasks WHERE priority BETWEEN enum_first(NULL::task_priority) AND enum_last(NULL::task_priority);

→All valid priority values (always true, useful for documentation/type safety)

enum_last

PG 8.3+→ anyenum

Returns the last value of the enum type.

DeveloperDBA

Signature

enum_last ( anyenum ) → anyenum

Parameters

Parameter	Type	Description
anyenum	anyenum	Any value of the enum type

Examples

sql

SELECT enum_last(NULL::mood);

→happy

sql

SELECT * FROM orders WHERE status = enum_last(NULL::order_status);

→Completed/final status orders

sql

SELECT id, status,
  CASE WHEN status = enum_last(NULL::order_status)
       THEN 'Terminal' ELSE 'In-progress' END AS phase
FROM orders;

→Labels each order as Terminal or In-progress

sql

DELETE FROM archive
WHERE stage < enum_last(NULL::pipeline_stage)
  AND created_at < now() - interval '1 year';

→Deletes old non-terminal pipeline records

⚠Anti-Pattern

If your enum represents workflow states and the last value is the terminal state, use `enum_last(NULL::enum_type)` to find completed items without hardcoding the state name.

example

SELECT count(*) FROM orders WHERE status = enum_last(NULL::order_status);

→Count of orders in the final state

enum_range

PG 8.3+→ anyarray

Returns all values of the enum type as an ordered array. With two arguments, returns values in the specified range (inclusive).

DeveloperDBA

Signatures

enum_range ( anyenum ) → anyarray

enum_range ( anyenum, anyenum ) → anyarray

Parameters

Parameter	Type	Description
anyenum	anyenum	NULL cast to enum type (1-arg form), or start/end values (2-arg form)

Examples

sql

SELECT enum_range(NULL::mood);

→{sad,ok,happy}

sql

SELECT enum_range('ok'::mood, 'happy'::mood);

→{ok,happy}

sql

SELECT unnest(enum_range(NULL::order_status)) AS status;

→All status values as rows

sql

SELECT s.val AS status, count(o.id) AS total
FROM unnest(enum_range(NULL::order_status)) AS s(val)
LEFT JOIN orders o ON o.status = s.val
GROUP BY 1
ORDER BY 1;

→All statuses with counts, including zero-count statuses

⚠Anti-Pattern

Use `SELECT unnest(enum_range(NULL::your_type)) AS val` to generate a complete list of enum values for LEFT JOINs in reports — ensures all categories appear even with zero counts.

example

SELECT s.val AS status, count(o.id) FROM unnest(enum_range(NULL::order_status)) AS s(val) LEFT JOIN orders o ON o.status = s.val GROUP BY 1 ORDER BY 1;

→All statuses with counts, including zero-count statuses

PostgreSQL Enum Type Functions

Jump to function

What are PostgreSQL Enum Type Functions?

enum_first

Signature

Parameters

Examples

enum_last

Signature

Parameters

Examples

enum_range

Signatures

Parameters

Examples

Related PostgreSQL Categories