From: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> |
---|---|
To: | Jitendra Loyal <jitendra(dot)loyal(at)gmail(dot)com>, Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Check constraints do not seem to be working!!! |
Date: | 2020-11-11 10:47:34 |
Message-ID: | 698d0c84-ff8a-4197-51d4-0a9772b998dd@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/11/20 10:06 AM, Jitendra Loyal wrote:
> Thanks Nikolay
>
> I read that but is there a way to meet the above requirement. And I will
> like to add that IS NULL and IS NOT NULL should evaluate to true/false.
> These operators are made for this and should not be returning NULL.
>
This has nothing to do with IS [NOT] NULL, it's the first part of the
expression (b = TRUE) causing trouble. Essentially, the constraint
(b = true) and (c is not null)
is evaluated in two steps. First we evaluate the two parts individually,
and for (null, true) the results would look like this:
(b = true) => null
(c is not null) => true
and then we combine those results using 'AND'
null AND true => null
which is considered as if the constraint matches. If you want to handle
NULL for the first expression, you may do this, for example:
(b it not null and b = true) and (c is not null)
Or something like that.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2020-11-11 10:48:30 | Re: Check constraints do not seem to be working!!! |
Previous Message | RAJAMOHAN | 2020-11-11 10:40:18 | Need to place pgpool logs on separate directory |