PostgreSQL Enum

Summary: in this tutorial, you’ll learn how to use the PostgreSQL enum data type to define a list of fixed values for a column.

PostgreSQL Enum Data Type Overview #

PostgreSQL allows you to define a column that stores a list of fixed values using an enum.

To create an enum, you use the CREATE TYPE statement with the following syntax:

CREATE TYPE enum_name 
AS 
ENUM(value1, value2, value3);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, provide enum’s name in the CREATE TYPE clause.
Second, list values of the enum within the parentheses after the ENUM keyword. Enum values are case-sensitive. A value is lower than the value that appears after it and higher than before.

To define a column with an enum type, you use the following syntax:

column_name enum_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The column_name can only store values defined in the enum_name. If you insert or update a value not in the list, PostgreSQL will issue an error.

PostgreSQL enum data type example #

First, create a new enum type called priority_type with three values: ‘low’, ‘medium’, and ‘high’

CREATE TYPE priority_type AS ENUM('low', 'medium', 'high');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create a table called transfer_orders that has the priority column whose data type is priority_type:

CREATE TABLE transfer_orders (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INT NOT NULL,
  from_warehouse INT NOT NULL,
  to_warehouse INT NOT NULL,
  quantity INT CHECK (quantity > 0),
  priority priority_type NOT NULL DEFAULT 'medium'
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, insert some rows into the transfer_orders table:

INSERT INTO
  transfer_orders (
    product_id,
    from_warehouse,
    to_warehouse,
    quantity,
    priority
  )
VALUES
  (1, 1, 2, 50, 'high'),
  (2, 2, 1, 20, 'low'),
  (2, 2, 1, 20, 'medium'),
  (3, 1, 2, 30, 'high')
RETURNING
  product_id,
  from_warehouse,
  to_warehouse,
  quantity,
  priority;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id | from_warehouse | to_warehouse | quantity | priority
------------+----------------+--------------+----------+----------
          1 |              1 |            2 |       50 | high
          2 |              2 |            1 |       20 | low
          2 |              2 |            1 |       20 | medium
          3 |              1 |            2 |       30 | high

Fourth, retrieve transfer orders and sort them by priority from high to low:

SELECT
  product_id,
  from_warehouse,
  to_warehouse,
  quantity,
  priority
FROM
  transfer_orders
ORDER BY
  priority DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fifth, find transfer orders with the high priority:

SELECT
  product_id,
  from_warehouse,
  to_warehouse,
  quantity,
  priority
FROM
  transfer_orders
WHERE
  priority = 'high';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

 product_id | from_warehouse | to_warehouse | quantity | priority
------------+----------------+--------------+----------+----------
          1 |              1 |            2 |       50 | high
          3 |              1 |            2 |       30 | high

Adding new values to enums #

You can use the ALTER TYPE statement to add a new value to an existing enum with the following syntax:

ALTER TYPE enum_name
ADD VALUE [IF NOT EXISTS] new_value
[[{BEFORE | AFTER } existing_value];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, provide the enum name you want to change in the ALTER TYPE clause.
Second, specify the new value in the ADD VALUE clause. Use the IF NOT EXISTS to conditionally add a new value only if it does not exist.
Third, define the new value’s position relative to an existing value. The default position is the end of the list.

The following example uses the ALTER TYPE to add the urgent value to the priority_type enum:

ALTER TYPE priority_type
ADD VALUE 'urgent';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Getting enum values #

The ENUM_RANGE() function accepts an enum and returns the enum values as an array:

ENUM_RANGE(null::enum_name)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following returns all values of the priority_type enum:

SELECT ENUM_RANGE(null::priority_type) priority_values;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     priority_values
--------------------------
 {low,medium,high,urgent}

Getting the first and last values of an enum #

The ENUM_FIRST and ENUM_LAST functions return the first and last values of an enum:

SELECT
  ENUM_FIRST(NULL::priority_type) first_priority,
  ENUM_LAST(NULL::priority_type) last_priority;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_priority | last_priority
----------------+---------------
 low            | urgentCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Renaming an enum value #

You can use the ALTER TYPE ... RENAME VALUE statement to change the value of an enum to the new one:

ALTER TYPE enum_name
RENAME VALUE existing_value TO new_value;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example changes ‘urgent’ to ‘very high’ for the priority_type column:

ALTER TYPE priority_type
RENAME VALUE 'urgent' TO 'very high';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Enum vs. Foreign key #

If you have a dynamic list of values, you should create a separate lookup table and set up a foreign key relationship between the main table and the lookup table.

A lookup table allows adding or removing values without modifying the main table. However, the SQL can be complex to write and slightly slower due to the need to join the two tables.

First, create a table called priorities:

CREATE TABLE priorities (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  priority VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, insert rows into the priorities table:

INSERT INTO
  priorities (priority)
VALUES
  ('low'),
  ('medium'),
  ('high')
RETURNING
  *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id | priority
----+----------
  1 | low
  2 | medium
  3 | high

Third, drop the transfer_orders table:

DROP TABLE IF EXISTS transfer_orders;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fourth, recreate a transfer_orders table that has the priority column linked to the priorities table:

CREATE TABLE transfer_orders (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INT NOT NULL,
  from_warehouse INT NOT NULL,
  to_warehouse INT NOT NULL,
  quantity INT CHECK (quantity > 0),
  priority_id INT NOT NULL,
  FOREIGN KEY (priority_id) REFERENCES priorities (id) ON DELETE CASCADE ON UPDATE CASCADE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fifth, insert rows into the transfer_orders table:

INSERT INTO
  transfer_orders (
    product_id,
    from_warehouse,
    to_warehouse,
    quantity,
    priority_id 
  )
VALUES
  (1, 1, 2, 50, 3),
  (2, 2, 1, 20, 1),
  (2, 2, 1, 20, 2),
  (3, 1, 2, 30, 3)
RETURNING
  product_id,
  from_warehouse,
  to_warehouse,
  quantity,
  priority_id ;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id | from_warehouse | to_warehouse | quantity | priority_id
------------+----------------+--------------+----------+-------------
          1 |              1 |            2 |       50 |           3
          2 |              2 |            1 |       20 |           1
          2 |              2 |            1 |       20 |           2
          3 |              1 |            2 |       30 |           3

Finally, query data from the transfer_orders table:

SELECT
  product_id,
  from_warehouse,
  to_warehouse,
  quantity,
  priority
FROM
  transfer_orders
  INNER JOIN priorities ON priorities.id = transfer_orders.priority_id
ORDER BY
  priority DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id | from_warehouse | to_warehouse | quantity | priority
------------+----------------+--------------+----------+----------
          2 |              2 |            1 |       20 | medium
          2 |              2 |            1 |       20 | low
          1 |              1 |            2 |       50 | high
          3 |              1 |            2 |       30 | highCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

An enum is a custom data type with a fixed list of values.
Use the CREATE TYPE statement to create a new enum.
Use the ALTER TYPE ... ADD VALUE statement to add a new value to an enum.
Use the ALTER TYPE ... RENAME VALUE to rename an enum value.

Quiz #

Was this tutorial helpful ?