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, Oct 02 '19 at 18:20
This solution in comment is the best answer, it's simple and logically correct. Works for me thanks :) — Shehzad Nizamani, Jul 14 '22 at 17:22

score 8 · Accepted Answer · answered Oct 02 '19 at 16:20

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

score 2 · Answer 2 · answered Oct 02 '19 at 15:46

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.

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

2 Answers2