ENUM types in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this ENUM insertion query?

Given the ENUM type mood with values ('happy', 'sad', 'neutral'), what will be the result of this insertion?

PostgreSQL

CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT, current_mood mood);
INSERT INTO person (name, current_mood) VALUES ('Alice', 'happy');
SELECT * FROM person;

A [{"id":1,"name":"Alice","current_mood":"happy"}]

B SyntaxError: ENUM type not recognized

C Error: invalid input value for enum mood

D [{"id":1,"name":"Alice","current_mood":null}]

💡 Hint

Remember ENUM values must be one of the predefined set.

The insertion uses a valid ENUM value 'happy', so the row is inserted correctly and returned.

🧠 Conceptual

intermediate

Which statement about ENUM types in PostgreSQL is true?

Choose the correct statement about ENUM types in PostgreSQL.

A ENUM types can be altered to add new values anywhere in the list without restrictions.

B ENUM types allow any string value without restrictions once created.

C ENUM types store values as integers internally and enforce allowed values.

D ENUM types are not supported in PostgreSQL.

💡 Hint

Think about how ENUM restricts values and stores them.

PostgreSQL ENUM types store values as integers but restrict values to the predefined set. Adding new values is possible but only at the end or with specific commands.

📝 Syntax

advanced

Which option correctly adds a new value 'excited' to an existing ENUM type 'mood'?

You have an ENUM type mood with values ('happy', 'sad', 'neutral'). Which SQL command correctly adds 'excited' as a new value?

A ALTER TYPE mood ADD VALUE 'excited';

B ALTER ENUM mood ADD 'excited';

C ALTER TYPE mood ADD 'excited' AFTER 'happy';

D ALTER TYPE mood ADD VALUE 'excited' AFTER 'happy';

💡 Hint

Check the exact syntax for adding a value at a specific position.

The correct syntax to add a new value to an ENUM type at a specific position is: ALTER TYPE mood ADD VALUE 'excited' AFTER 'happy';

❓ optimization

advanced

What is a key advantage of using ENUM types over VARCHAR for fixed sets of values?

Why might you choose an ENUM type instead of VARCHAR for a column with limited possible values?

A ENUM types use less storage and improve query performance by storing values as integers internally.

B ENUM types allow any string value, so they are more flexible than VARCHAR.

C ENUM types automatically index the column without extra commands.

D ENUM types do not enforce value restrictions, making inserts faster.

💡 Hint

Think about storage and validation differences.

ENUM types store values internally as integers referencing the allowed strings, which can save space and speed up comparisons compared to VARCHAR.

🔧 Debug

expert

Why does this INSERT fail with an error?

Given the ENUM type status with values ('new', 'in_progress', 'done'), why does this query fail? INSERT INTO tasks (id, status) VALUES (1, 'completed');

PostgreSQL

CREATE TYPE status AS ENUM ('new', 'in_progress', 'done');
CREATE TABLE tasks (id INT PRIMARY KEY, status status);
INSERT INTO tasks (id, status) VALUES (1, 'completed');

A Syntax error due to missing quotes around 'completed'.

B Error because 'completed' is not a valid value in the ENUM 'status'.

C Error because the table 'tasks' does not exist.

D No error; the row is inserted with 'completed' as status.

💡 Hint

Check if the inserted value matches the ENUM allowed values.

The ENUM type 'status' only allows 'new', 'in_progress', and 'done'. 'completed' is not in the list, so the insert fails with an error.