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

user_3574379 0
Viewed 820

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

check-constraints ddl postgresql
2 Answers

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

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.

user_21697620
Related