ENUM types in PostgreSQL

Introduction

ENUM types let you store a fixed set of values in a column. This helps keep data clean and consistent.

When you want to store a list of fixed options like days of the week.

When you want to limit a column to specific values like 'small', 'medium', 'large'.

When you want to make sure users can only pick from allowed choices.

When you want to save space compared to using text columns for fixed sets.

When you want the database to help prevent invalid data entries.

Syntax

PostgreSQL

CREATE TYPE enum_name AS ENUM ('value1', 'value2', 'value3');

CREATE TABLE table_name (
  column_name enum_name
);

You create the ENUM type once, then use it in table columns.

ENUM values are stored internally as labels, not numbers.

Examples

This creates a mood type with three options and uses it in a person table.

PostgreSQL

CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');

CREATE TABLE person (
  name TEXT,
  current_mood mood
);

This defines sizes for t-shirts and uses the ENUM in the size column.

PostgreSQL

CREATE TYPE size AS ENUM ('small', 'medium', 'large');

CREATE TABLE tshirt (
  id SERIAL PRIMARY KEY,
  size size
);

Sample Program

This example creates an ENUM for traffic lights, inserts all values, and selects them.

PostgreSQL

CREATE TYPE traffic_light AS ENUM ('red', 'yellow', 'green');

CREATE TABLE signals (
  id SERIAL PRIMARY KEY,
  light traffic_light
);

INSERT INTO signals (light) VALUES ('red'), ('green'), ('yellow');

SELECT * FROM signals ORDER BY id;

Important Notes

You cannot insert values not listed in the ENUM.

To add new values to an ENUM, you must use ALTER TYPE.

ENUMs improve data integrity by restricting allowed values.

Summary

ENUM types store a fixed set of allowed values.

They help keep data consistent and clean.

Create ENUM types once, then use them in table columns.