Обсуждение: Check constraints do not seem to be working!!!
Consider this table definition:
create table t ( i serial, b bool, c bool,
constraint b_c check ( (b = true and c is not null ) or (b is distinct
from true and c is null) )
constraint b_c check ( (b = true and c is not null ) or (b = false and c
is null) or (b is null and c is null) )
);
Despite the above two constraints, the following rows get into the table:
insert into t (b , c) values (null, true), (null, false);
create table t ( i serial, b bool, c bool,
constraint b_c check ( (b = true and c is not null ) or (b is distinct
from true and c is null) )
constraint b_c check ( (b = true and c is not null ) or (b = false and c
is null) or (b is null and c is null) )
);
Despite the above two constraints, the following rows get into the table:
insert into t (b , c) values (null, true), (null, false);
On 11/11/2020 06:44, Jitendra Loyal wrote:
Consider this table definition:
create table t ( i serial, b bool, c bool,
constraint b_c check ( (b = true and c is not null ) or (b is distinct
from true and c is null) )
constraint b_c check ( (b = true and c is not null ) or (b = false and c
is null) or (b is null and c is null) )
);
Despite the above two constraints, the following rows get into the table:
insert into t (b , c) values (null, true), (null, false);
(b = TRUE AND c IS NOT NULL) evaluates to null when b is null Cheers, Chris Sterritt
st 11. 11. 2020 v 9:38 odesílatel Chris Sterritt <chris.sterritt@yobota.xyz> napsal:
On 11/11/2020 06:44, Jitendra Loyal wrote:Consider this table definition:
create table t ( i serial, b bool, c bool,
constraint b_c check ( (b = true and c is not null ) or (b is distinct
from true and c is null) )
constraint b_c check ( (b = true and c is not null ) or (b = false and c
is null) or (b is null and c is null) )
);
Despite the above two constraints, the following rows get into the table:
insert into t (b , c) values (null, true), (null, false);
(b = TRUE AND c IS NOT NULL) evaluates to null when b is null
yes, constraint is violated only when result is false, no when it is null.
Regards
Pavel
Cheers, Chris Sterritt
On Wed, Nov 11, 2020 at 12:26 AM Jitendra Loyal <jitendra.loyal@gmail.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.
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.
Regards,
Jitendra
On Wed 11 Nov, 2020, 14:18 Nikolay Samokhvalov, <samokhvalov@gmail.com> wrote:
On Wed, Nov 11, 2020 at 12:26 AM Jitendra Loyal <jitendra.loyal@gmail.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.
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
On 11 Nov 2020, at 11:15, Jitendra Loyal <jitendra.loyal@gmail.com> wrote:
Thanks NikolayI 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@gmail.com> wrote:On Wed, Nov 11, 2020 at 12:26 AM Jitendra Loyal <jitendra.loyal@gmail.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.
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.vondra@enterprisedb.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
On Wed, 2020-11-11 at 11:47 +0100, Tomas Vondra wrote: > you may do this, for example: > > (b it not null and b = true) and (c is not null) > > Or something like that. My (equivalent) suggestion: b IS TRUE AND c IS NOT NULL Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Thanks Laurenz
This is interesting...b is True
Thanks and regards,
Jitendra
On Wed 11 Nov, 2020, 22:52 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
On Wed, 2020-11-11 at 11:47 +0100, Tomas Vondra wrote:
> you may do this, for example:
>
> (b it not null and b = true) and (c is not null)
>
> Or something like that.
My (equivalent) suggestion:
b IS TRUE AND c IS NOT NULL
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com