Re: Check constraints do not seem to be working!!!

Thanks Tomas....

Understood... My bad.... Was just not looking at that aspect

Thanks once again,
Regards,
Jitendra

On Wed, 11 Nov 2020 at 16:17, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
wrote:

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