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.

Practice

1. What is the main purpose of using ENUM types in PostgreSQL?

easy

A. To restrict a column to a fixed set of allowed values

B. To store large text data efficiently

C. To create temporary tables

D. To index numeric columns faster

Solution

1. Step 1: Understand ENUM type purpose

   ENUM types define a list of allowed values for a column, ensuring data consistency.

2. Step 2: Compare with other options

   Other options describe unrelated features like text storage, temporary tables, or indexing.

3. Final Answer:

   To restrict a column to a fixed set of allowed values -> Option A

4. Quick Check:

   ENUM = fixed allowed values [OK]

Hint: ENUM limits values to a fixed list, ensuring consistency [OK]

Common Mistakes:

• Thinking ENUM stores large text data
• Confusing ENUM with temporary tables
• Assuming ENUM improves indexing speed

2. Which of the following is the correct syntax to create an ENUM type named mood with values 'happy', 'sad', and 'neutral'?

easy

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

B. CREATE ENUM mood ('happy', 'sad', 'neutral');

C. CREATE TYPE mood ENUM ['happy', 'sad', 'neutral'];

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

Solution

1. Step 1: Recall ENUM creation syntax

   The correct syntax is CREATE TYPE name AS ENUM (values); with values in parentheses and single quotes.

2. Step 2: Check each option

   CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral'); matches the correct syntax exactly. Others have wrong keywords or brackets.

3. Final Answer:

   CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral'); -> Option D

4. Quick Check:

   CREATE TYPE ... AS ENUM (values) [OK]

Hint: Use CREATE TYPE name AS ENUM (values) syntax [OK]

Common Mistakes:

• Using CREATE ENUM instead of CREATE TYPE
• Using square brackets instead of parentheses
• Omitting AS keyword

3. Given the ENUM type and table below, what will be the result of the query?

CREATE TYPE colors AS ENUM ('red', 'green', 'blue');
CREATE TABLE items (id SERIAL PRIMARY KEY, color colors);
INSERT INTO items (color) VALUES ('green'), ('blue'), ('red');

SELECT color FROM items ORDER BY color;

medium

A. red, blue, green

B. blue, green, red

C. green, blue, red

D. red, green, blue

Solution

1. Step 1: Understand ENUM ordering

   ENUM values are ordered by their declaration order: 'red' < 'green' < 'blue'.

2. Step 2: Apply ORDER BY on color column

   Ordering by color sorts rows as per ENUM order, so 'red', 'green', 'blue'.

3. Final Answer:

   red, green, blue -> Option A

4. Quick Check:

   ENUM order = declaration order [OK]

Hint: ENUM sorts by declared order, not alphabetically [OK]

Common Mistakes:

• Assuming alphabetical order instead of ENUM order
• Confusing insertion order with sort order
• Expecting default text sorting

4. What is wrong with the following SQL code?

CREATE TYPE status AS ENUM ('new', 'in_progress', 'done');
CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  task_status status DEFAULT 'pending'
);

medium

A. ENUM types cannot be used as column types

B. Missing semicolon after CREATE TYPE statement

C. The default value 'pending' is not in the ENUM list

D. The SERIAL keyword is deprecated

Solution

1. Step 1: Check ENUM values and default

   ENUM 'status' has values 'new', 'in_progress', 'done'. Default 'pending' is not listed.

2. Step 2: Understand default value constraints

   Default must be one of ENUM values; otherwise, it causes an error.

3. Final Answer:

   The default value 'pending' is not in the ENUM list -> Option C

4. Quick Check:

   Default must be ENUM member [OK]

Hint: Default must be one of ENUM values [OK]

Common Mistakes:

• Assuming any string can be default
• Thinking ENUM can't be column type
• Ignoring missing semicolon errors

5. You want to add a new value 'archived' to an existing ENUM type status with values ('new', 'in_progress', 'done'). Which statement correctly adds 'archived' after 'done'?

hard

A. ALTER TYPE status ADD VALUE 'archived' BEFORE 'in_progress';

B. ALTER TYPE status ADD VALUE 'archived' AFTER 'done';

C. ALTER TYPE status ADD VALUE 'archived' BEFORE 'done';

D. ALTER TYPE status MODIFY VALUE 'archived' AFTER 'done';

Solution

1. Step 1: Recall how to add ENUM values

   PostgreSQL uses ALTER TYPE ... ADD VALUE 'new_value' [BEFORE|AFTER existing_value] syntax.

2. Step 2: Identify correct position

   To add 'archived' after 'done', use AFTER 'done'.

3. Step 3: Check options

   ALTER TYPE status ADD VALUE 'archived' AFTER 'done'; matches correct syntax and position. Others use BEFORE (wrong position) or invalid keywords.

4. Final Answer:

   ALTER TYPE status ADD VALUE 'archived' AFTER 'done'; -> Option B

5. Quick Check:

   ALTER TYPE ADD VALUE ... AFTER ... [OK]

Hint: Use ALTER TYPE ADD VALUE 'val' AFTER 'existing' to position [OK]

Common Mistakes:

• Using MODIFY instead of ADD VALUE
• Placing new value BEFORE wrong existing value
• Using wrong position like BEFORE 'done'