In Postgres, how do you restrict possible values for a particular column?

Question

I want to create a column element_type in a table (called discussion) that allows the text values "lesson" or "quiz" but will generate an error if any other value is inserted into that column.

I understand that I could create a separate table called element_types with columns element_id (primary key, int) and element_type (unique, text) and create a foreign key foreign_element_id in the table discussion referencing element_types's column element_id. Or alternatively, I could forget element_id altogether and just set element_type as the primary key. But I want to avoid creating a new table.

Is there a more straightforward way to restrict possible values in a column without creating a new table?

Answer 1

You could add a CHECK CONSTRAINT:

ALTER TABLE distributors     ADD CONSTRAINT check_types     CHECK (element_type = 'lesson' OR element_type = 'quiz'); 

Although IMO the cleaner option would be to create an ENUM:

CREATE TYPE element_type AS ENUM ('lesson', 'quiz');