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

Restrictions 101: CHECK and ENUM

Value checking with Enums

An ENUM type in PostgreSQL helps enforce data consistency across tables while providing a clear semantic understanding. It's like a vip list at a data nightclub:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');// Who let 'soso' in? Not on the list... bounce them!


CHECK yourself before you wreck your database

A CHECK constraint can do more than just simple list restrictions, it can enforce specific patterns using regular expressions:

ALTER TABLE products 
ADD CONSTRAINT product_code_check 
CHECK (code ~ '^P-[0-9]{4}$'); // "P-12345", you're good. "P-1234A" ... boo! Invalid.


This constraint ensures the product code starts with 'P-' followed by a 4-digit number.

Bring out the big guns: triggers and functions

When CHECK constraints just won't cut it, Triggers offer a more dynamic way to enforce column value restrictions:

CREATE FUNCTION validate_product_price() 
RETURNS trigger AS $$
BEGIN
  IF NEW.price < 0 THEN
    RAISE EXCEPTION 'Price cannot be negative';--C'mon, even Stingy McStingface knows prices can't be in the negatives! 
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_before_insert 
BEFORE INSERT OR UPDATE ON product 
FOR EACH ROW 
EXECUTE FUNCTION validate_product_price();//  Fine, take my money... but not my negative money


Proceed with caution, as power comes with complexity!

ENUM vs. CHECK: The ongoing saga

ENUM: ideal for fixed value sets

ENUM types shine when value lists are more or less immutable. Modifying ENUMs can be like trying to unscramble an egg, so use wisely.

CREATE TYPE legal_status AS ENUM ('guilty', 'not_guilty', 'pending');//...'kinda guilty'? Hmm, no.


CHECK: Change is the only constant

If you're in a fast-paced, fluid environment where change is the norm, CHECK constraints could become your best friend:

ALTER TABLE your_table 
DROP CONSTRAINT if_exists change_friendly_check;-- Bye-bye old rules, hello new ones!
ALTER TABLE your_table 
ADD CONSTRAINT change_friendly_check 
CHECK (new_condition_here);-- "Always flexible, never breakable", CHECK mantra


Performance: ENUM vs. Check Constraint

Performance-wise, ENUM types can be faster due to their use of integer indexing. However, in the élan of the real world, you probably wouldn't even notice the difference.