ENUM types in PostgreSQL - Step-by-Step Execution

Concept Flow - ENUM types

Define ENUM type with values

↓

Create table using ENUM column

↓

Insert rows with ENUM values

↓

Query table to see ENUM values

↓

Use ENUM values in conditions or sorting

↓

END

First, define an ENUM type with allowed values, then create a table using that ENUM type. Insert rows using those ENUM values, and finally query or use them in conditions.

Execution Sample

PostgreSQL

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (name TEXT, current_mood mood);
INSERT INTO person VALUES ('Alice', 'happy');
INSERT INTO person VALUES ('Bob', 'sad');
SELECT * FROM person;

This code defines an ENUM type 'mood', creates a table with a column of that type, inserts rows with ENUM values, and selects all rows.

Execution Table

Step	Action	Evaluation/Result	Output/Effect
1	CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')	Type 'mood' created with values: sad, ok, happy	ENUM type 'mood' ready to use
2	CREATE TABLE person (name TEXT, current_mood mood)	Table 'person' created with ENUM column	Table schema includes ENUM column
3	INSERT INTO person VALUES ('Alice', 'happy')	Value 'happy' is valid ENUM	Row inserted: ('Alice', 'happy')
4	INSERT INTO person VALUES ('Bob', 'sad')	Value 'sad' is valid ENUM	Row inserted: ('Bob', 'sad')
5	SELECT * FROM person	Returns all rows with ENUM values	[('Alice', 'happy'), ('Bob', 'sad')]
6	INSERT INTO person VALUES ('Eve', 'excited')	Error: 'excited' not in ENUM	Insert fails with error
7	SELECT * FROM person WHERE current_mood = 'happy'	Filters rows with ENUM 'happy'	[('Alice', 'happy')]
8	SELECT * FROM person ORDER BY current_mood	Sorts rows by ENUM order (sad < ok < happy)	[('Bob', 'sad'), ('Alice', 'happy')]
9	DROP TYPE mood	Error: cannot drop type mood because other objects depend on it	Cannot drop if table uses it; error if tried now

💡 Execution stops after all steps; errors occur if invalid ENUM values used or type dropped while in use.

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	After Step 8
person table rows	empty	[('Alice', 'happy')]	[('Alice', 'happy'), ('Bob', 'sad')]	[('Alice', 'happy'), ('Bob', 'sad')]	[('Alice', 'happy'), ('Bob', 'sad')]	[('Alice', 'happy'), ('Bob', 'sad')]	[('Alice', 'happy'), ('Bob', 'sad')]

Key Moments - 3 Insights

Why does inserting ('Eve', 'excited') fail?

Because 'excited' is not one of the allowed ENUM values ('sad', 'ok', 'happy'), as shown in execution_table step 6.

Can we drop the ENUM type 'mood' after creating the table?

No, because the table 'person' uses the ENUM type. Dropping it will cause an error, as noted in step 9.

How does ordering by ENUM values work?

Rows are sorted by the order of ENUM values as defined ('sad' < 'ok' < 'happy'), shown in step 8.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output after inserting ('Bob', 'sad')?

A [('Alice', 'happy'), ('Bob', 'sad')]

B [('Bob', 'sad')]

C [('Alice', 'happy')]

D Insert fails with error

💡 Hint

Check the output column at step 4 in the execution_table.

At which step does the insert fail due to invalid ENUM value?

A Step 3

B Step 6

C Step 7

D Step 9

💡 Hint

Look for the step where the output says 'Insert fails with error'.

If we add 'excited' to ENUM values, what changes in the execution_table?

A Step 8 sorting order changes

B Step 9 drop type succeeds immediately

C Step 6 insert would succeed

D No change at all

💡 Hint

Think about what causes the insert failure at step 6.

Concept Snapshot

ENUM types in PostgreSQL:
- Define with CREATE TYPE name AS ENUM ('val1', 'val2', ...);
- Use ENUM type as column data type in tables
- Only allowed ENUM values can be inserted
- ENUM values have a defined order for sorting
- Cannot drop ENUM type if used by tables

Full Transcript

ENUM types in PostgreSQL let you create a custom list of allowed string values. You first define the ENUM type with specific values. Then you create a table with a column using that ENUM type. When inserting data, only the predefined ENUM values are allowed. Trying to insert a value not in the ENUM causes an error. You can query and filter rows by ENUM values. Sorting by ENUM column uses the order defined in the ENUM. You cannot drop the ENUM type if any table still uses it. This step-by-step trace showed creating the ENUM, creating a table, inserting valid and invalid values, querying, sorting, and the error when dropping the type while in use.