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?

why do you want to avoid creating a table? they are not particularly costly. — SingleNegationElimination
– SingleNegationElimination, Commented Aug 31, 2011 at 0:41
It's not so much the cost of a table as much as unnecessary clutter whenever I want to restrict possible values, which in my case, is very frequently. — Deets McGeets
– Deets McGeets, Commented Aug 31, 2011 at 4:34

mu is too short · Accepted Answer · 2011-08-31 01:29:27Z

149

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');

edited Aug 31, 2011 at 1:29

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Aug 30, 2011 at 23:40

NullUserException

85.8k31 gold badges212 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Deets McGeets Over a year ago

Excellent. Thank you. As a note to others who may be as new to Postgres as I am, CREATE TYPE creates a user-defined type to be used as any other type such as int or text may be used. In this case, one could say: CREATE TYPE element_type as ENUM ('lesson', 'quiz'); CREATE TABLE discussion ( type_of_element element_type; );

Darren Over a year ago

Worth noting that while you can add values to ENUM types as of recent releases, there is no graceful/easy way to remove them. If you anticipate your allowable values changing a lot, I recommend going with the check constraint.

Sergiy Dakhniy Over a year ago

We had a problem of mapping postgresql enums to Java enums and decided to move on with 'check'

julaine Sep 1 at 9:49

@Darren the same is true for the check-constraint, is it not? To remove allowed values you must first delete/edit all data containing the value.

Community · Accepted Answer · 2025-09-01 08:25:06Z

A shorcut syntax is:

ALTER TABLE distributors ADD CONSTRAINT check_types CHECK (element_type IN ('lesson', 'quiz') );

This translates automatically to:

CONSTRAINT check_types CHECK (element_type::text = ANY (ARRAY['lesson'::character varying, 'quiz'::character varying]) )

typo: needs a close square bracket on right side after last 'character varying'

escrubalo · Accepted Answer · 2011-08-30 23:52:45Z

This trigger throws an exception whenever someone try to insert or update a row with an invalid element_type.

CREATE OR REPLACE FUNCTION check_discussion_element_type() RETURNS TRIGGER AS $$ DECLARE new_element_type varchar(25); BEGIN SELECT element_type into new_element_type FROM discussion WHERE discussion.element_id = NEW.element_id; IF new_element_type != 'lesson' AND new_element_type != 'quiz' THEN RAISE EXCEPTION 'Unexpected discussion type'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; create trigger t_check_discussion_element_type after update or insert on discussion for each row execute procedure check_discussion_element_type();

If you want to remove the hard-coded types you can adapt it to check if the new type exists in a type table.

Very slow and complex solution, compared to a check constraint or ENUM.
This has the (dis?)advantage of allowing existing values outside the range to remain the same and only affecting future values.

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Linked

Related