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

On 11 Nov 2020, at 11:15, Jitendra Loyal <jitendra(dot)loyal(at)gmail(dot)com> 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.

That is exactly what they do. Your problem is with the equality operator and its behaviour with NULL values, which is described in the referenced document.

--
If you can't see the forest for the trees,
Cut the trees and you'll find there is no forest.

>> On Wed 11 Nov, 2020, 14:18 Nikolay Samokhvalov, <samokhvalov(at)gmail(dot)com> wrote:
>>> On Wed, Nov 11, 2020 at 12:26 AM Jitendra Loyal <jitendra(dot)loyal(at)gmail(dot)com> wrote:
>>> Despite the above two constraints, the following rows get into the table:
>>> insert into t (b , c) values (null, true), (null, false);
>>
>> This behavior is described in the docs https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS:
>>
>> > It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. To ensure that a column does not contain null values, the not-null constraint described in the next section can be used.