ENUM types in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an ENUM type in PostgreSQL?

An ENUM type is a special data type that allows you to define a list of allowed values. It helps to store only one of these predefined values in a column.

beginner

How do you create an ENUM type in PostgreSQL?

Use the command: CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral'); This creates a new ENUM type named 'mood' with three allowed values.

beginner

Can you insert a value not listed in an ENUM type column?

No, PostgreSQL will reject any value that is not one of the predefined ENUM values. This ensures data consistency.

intermediate

How do you add a new value to an existing ENUM type?

Use ALTER TYPE mood ADD VALUE 'excited'; to add a new allowed value 'excited' to the ENUM type 'mood'.

intermediate

Why use ENUM types instead of text columns?

ENUM types restrict values to a fixed set, preventing invalid data. They also use less storage and can improve query speed compared to text columns.

Which command creates a new ENUM type in PostgreSQL?

A CREATE TABLE status ENUM ('open', 'closed');

B CREATE ENUM status ('open', 'closed');

C CREATE TYPE status AS ENUM ('open', 'closed');

D ALTER TYPE status ADD ENUM ('open', 'closed');

The correct syntax to create an ENUM type is using CREATE TYPE with AS ENUM.

What happens if you try to insert a value not in the ENUM list?

A The value is converted to the closest ENUM value.

B The value is inserted as NULL.

C The value is automatically added to the ENUM list.

D PostgreSQL throws an error and rejects the insert.

PostgreSQL enforces ENUM values strictly and rejects any value not in the list.

How do you add a new value to an existing ENUM type?

A ALTER TYPE mood ADD VALUE 'excited';

B CREATE TYPE mood ADD VALUE 'excited';

C UPDATE TYPE mood SET VALUE = 'excited';

D ALTER TABLE mood ADD VALUE 'excited';

ALTER TYPE with ADD VALUE is the correct way to add new ENUM values.

Which of these is NOT an advantage of ENUM types?

A Restricting column values to a fixed set.

B Automatically updating ENUM values on insert.

C Using less storage than text columns.

D Improving query speed for fixed sets.

ENUM values must be added explicitly; they are not updated automatically.

What data type would you use to store a user's mood with fixed options?

A ENUM

B INTEGER

C TEXT

D BOOLEAN

ENUM is ideal for fixed sets of string values like moods.

Explain what an ENUM type is and how it helps maintain data quality in PostgreSQL.

Think about how you limit choices in a form.

Describe the steps to create an ENUM type and add a new value to it in PostgreSQL.

Start with creating, then modifying the ENUM.