CODEWITHSUNDEEP
sqlpostgresql
Deets McGeets
Deets McGeets

Reputation: 6387

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

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?

Upvotes: 93

Views: 60424

Answers (3)

ffrey
ffrey

Reputation: 791

A shorcut syntax is : 

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

This translates automaticolly to : 

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

Enjoy ;-)

Upvotes: 67

NullUserException
NullUserException

Reputation: 85468

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

Upvotes: 147

escrubalo
escrubalo

Reputation: 338

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.

Upvotes: 0

Related Questions