How do I create a IF statement creating a table in Postgres?

Question

I'm creating a table and I need a check constraint to validate the posibles values given a string value. I'm creating this table:

CREATE TABLE cat_accident (
    acc_type VARCHAR(30) NOT NULL CHECK(acc_type = 'Home accident' OR acc_type = 'Work accident'),
    acc_descrip VARCHAR(30) NOT NULL    
);

So basically I want to validate if acc_type is equal to Home accident, then acc_descrip can be or 'Intoxication' OR 'burns' OR 'Kitchen wound', OR if acc_type is equal to Work Accident, then acc_descrip can be OR 'freezing' OR 'electrocution'.

How do I write that constraint?

Answer 1

Use a CHECK constraint with a CASE expression:

CREATE TABLE cat_accident (
    acc_type VARCHAR(30) NOT NULL,
    acc_descrip VARCHAR(30) NOT NULL 
    CHECK(
      CASE acc_type
        WHEN 'Home accident' THEN acc_descrip IN ('Intoxication', 'burns', 'Kitchen wound')
        WHEN 'Work accident' THEN acc_descrip IN ('freezing', 'electrocution')
      END
    )
);

See the demo.