Why `IF` clause is not possible in postgres `CHECK` constraint?

Question

for example is this query possible?

CREATE TABLE products (
    name text,
    price numeric not null,
    CHECK (IF price > 0 THEN name IS NOT NULL ELSE name IS NULL END IF)
);

UPDATE:

seems like no

here https://rextester.com/l/postgresql_online_compiler

it throws error

Error(s), warning(s):

42601: syntax error at or near "price"

looking at documentation https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE it says

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. The system column tableoid may be referenced, but not any other system column.

but IF is not subquery, dont understand why it doesnt work

UPDATE 2:

the

CREATE TABLE products (
    name text,
    price numeric not null,
    CHECK ((price > 0 AND name IS NOT NULL) OR (price <= 0 AND name IS NULL))
);

works, but it makes tedious to write complex queries this way

In your specific case ((price > 0) = (name IS NOT NULL)) would be the most succinct way, you might want to choose an example where the then and else branches are unrelated. — Bergi
– Bergi, Commented Oct 2, 2019 at 18:20
This solution in comment is the best answer, it's simple and logically correct. Works for me thanks :) — Shehzad Nizamani
– Shehzad Nizamani, Commented Jul 14, 2022 at 17:22

jjanes · Accepted Answer · 2019-10-02 16:20:20Z

9

IF is not a subquery, and it is not anything else either, in SQL. So it is assumed to be a column name. Having two (assumed) column names immediately in a row is a syntax error, and is assigned to the second column name.

SQL has CASE, not IF. You need to use the language you are using, not just make up things you hope to work.

CREATE TABLE products (
    name text,
    price numeric not null,
    CHECK (case when price > 0 THEN name IS NOT NULL ELSE name IS NULL END)
);

answered Oct 2, 2019 at 16:20

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

Comments

Martin · Accepted Answer · 2019-10-02 15:46:33Z

2

I'm not 100% on what you're asking, but I think you're saying:

If the price > 0 then name CANNOT be NULL

In which case this should do it:

CHECK (price > 0 AND name IS NOT NULL)

If the name can be NULL on price being 0 then use this:

CHECK ((price > 0 AND name IS NOT NULL) OR (price = 0 AND name IS NULL))

You don't need to specify IF in the CHECK condition, it should essentially contain the actual statement to be tested rather than an IF statement.

answered Oct 2, 2019 at 15:46

Martin

16.5k1 gold badge33 silver badges51 bronze badges

Collectives™ on Stack Overflow

Why `IF` clause is not possible in postgres `CHECK` constraint?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related