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?

You could probably cobble something together using [CASE](https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE) though it would mean altering the table anytime the conditions changed. I would think a multi-column `FOREIGN KEY` with `MATCH FULL` on `acc_type` and `acc_descrip` would be easier. Then you just have to update the referenced table. — Adrian Klaver, Jan 08 '22 at 23:35

forpas · Accepted Answer · 2022-01-09T00:09:02.053

2

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.

edited Jan 09 '22 at 00:09

answered Jan 08 '22 at 23:56

forpas

160,666
10
38
76

score 1 · Answer 2 · answered Jan 09 '22 at 04:29

I'd suggest implementing this with a lookup table:

CREATE TABLE l_accident_description(
    description_id    VARCHAR(5) PRIMARY KEY,
    description_full  VARCHAR(30) NOT NULL UNIQUE,
    location          VARCHAR(30)
);
INSERT INTO l_accident_description
    (description_id,description_full,location)
VALUES
    ('INTOX','Intoxication','Home Accident'),
    ('BURNS','Burns','Home Accident'),
    ('K_WND','Kitchen wound','Home Accident'),
    ('FREEZ','Freezing','Work Accident'),
    ('ELECT','Electrocution','Work Accident');

That way you can encode the relationship you want to encode into cat_accident, but if the details ever change, it's only a matter of inserting/deleting/updating rows in your lookup table. This implementation has the added benefit that you're not storing as much data repetitively in your table (just a VARCHAR(5) code rather than a VARCHAR(30) string). The table construction then becomes (with added primary key):

CREATE TABLE cat_accident (
    cat_accident_id PRIMARY KEY,
    acc_descrip VARCHAR(5) NOT NULL REFERENCES l_accident_description(description_id)
);

Any time you wanted to know whether the accident Home/Work, this could be accomplished with a query joining the lookup table. Joining lookup tables is more in the spirit of good database construction, rather than hard-coding checks to tables that may easily change or grow more complex as the database grows.

In fact, the ideal solution might be to create two lookup tables here, with l_accident_description in turn referencing a location lookup, but for simplicity's sake I've shown how it might be accomplished with one.

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

2 Answers2