Postgresql Constraint CHECK conditional

Question

I have a table with columns: c1, c2, c3, c4 And I am trying to add a CONSTRAINT check The conditions are:

if c4 is not null then c1,c2,c3 all must not null.
else if c4 is null then c1,c2,c3 are optional but at least one field must be non-null.

Here is my sql command:

ADD CONSTRAINT quatereulchk CHECK( 
    (c1 IS NOT NULL AND c2 IS NOT NULL AND c3 IS NOT NULL AND c4 IS NOT NULL) 
    OR 
    (c4 IS NULL AND c1 IS NOT NULL OR c2 IS NOT NULL OR c3 IS NOT NULL)
    );

I tested with pg_admin however the constrain is not working. Thanks!

Hint: check precedence of AND and OR. If we talked about maths your DDL expects that 2 * 2 + 2 equals to 8 — zerkms
– zerkms, Commented Nov 16, 2015 at 21:28
I see, in my case is that means AND is higher than OR? Thank you for your hint! — bf39L
– bf39L, Commented Nov 16, 2015 at 21:54
"BTW really like the way of answering question, giving hint instead of giving answer." --- that's nice to hear :-) Unfortunately very few people here respect that — zerkms
– zerkms, Commented Nov 16, 2015 at 22:06
The website is based on the idea of answering questions. It's hard to blame people that follow that plan. — klin
– klin, Commented Nov 16, 2015 at 22:26

Manuel Saucedo · Accepted Answer · 2015-11-16 21:31:03Z

4

Try grouping the "c1 IS NOT NULL OR c2 IS NOT NULL OR c3 IS NOT NULL" in the second case, for example:

ADD CONSTRAINT quatereulchk CHECK( (c1 IS NOT NULL AND c2 IS NOT NULL AND c3 IS NOT NULL AND c4 IS NOT NULL) OR (c4 IS NULL AND (c1 IS NOT NULL OR c2 IS NOT NULL OR c3 IS NOT NULL)) )

answered Nov 16, 2015 at 21:31

Manuel Saucedo

5253 silver badges18 bronze badges

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

Comments

klin · Accepted Answer · 2015-11-16 21:56:50Z

2

Use coalesce():

ADD CONSTRAINT quatereulchk CHECK( 
    (c1 IS NOT NULL AND c2 IS NOT NULL AND c3 IS NOT NULL AND c4 IS NOT NULL) 
    OR 
    (c4 IS NULL AND COALESCE(c1, c2, c3) IS NOT NULL)
    );

Some tests:

insert into example values
(null, null, null, null);
ERROR:  new row for relation "example" violates check constraint "quatereulchk"
DETAIL:  Failing row contains (null, null, null, null).

insert into example values
(null, null, 3, null);
INSERT 0 1

insert into example values
(null, 2, 3, 4);
ERROR:  new row for relation "example" violates check constraint "quatereulchk"
DETAIL:  Failing row contains (null, 2, 3, 4).

insert into example values
(1, 2, 3, 4);
INSERT 0 1

edited Nov 16, 2015 at 21:56

answered Nov 16, 2015 at 21:39

klin

123k15 gold badges240 silver badges262 bronze badges

6 Comments

bf39L Over a year ago

Thank you for your reply I tries with c1,c2,c3,c4 => (1.1, null, null,null) and its not working

klin Over a year ago

Ops, just a typo, should be coalesce(c1, c2, c3). The answer edited.

bf39L Over a year ago

Now it works for me ;D. Just my curiosity. Between Manuel Saucedo's answer above and your answer, which way is faster?

zerkms Over a year ago

@Lester it does not matter

klin Over a year ago

@Lester - I do not think there is a significant difference.

|

Collectives™ on Stack Overflow

Postgresql Constraint CHECK conditional

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related