Postgres Pro
Downloads
Home   >   mailing lists

Thread: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions

Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions

From
Jian He
Date:

If the question is stupid, please tell me why. 

postgresql - How to query check the constraint is valid or not - Stack Overflow
Postgres 14 | db<>fiddle (dbfiddle.uk)

CREATE TABLE emp (test_check int check ( test_check >1 and test_check < 0 ));

alter table emp  VALIDATE CONSTRAINT emp_test_check_check;```

select * from pg_constraint where conname = 'emp_test_check_check';

Even with the above mentioned step, does postgresql know above check constraint condition always false.


another similar question:
can postgresql deduce from
CREATE TABLE emp1 (test_check int check ( test_check >1 and test_check > 10 ));

to

CREATE TABLE emp1 (test_check int check ( test_check > 10 ));

Re: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions

From
Tom Lane
Date:
Jian He <hejian.mark@gmail.com> writes:
> CREATE TABLE emp (test_check int check ( test_check >1 and test_check
> < 0 ));

> Even with the above mentioned step, does postgresql know above check
> constraint condition always false.

No, not explicitly.  If the constraint always fails, that is
your problem not Postgres'.

            regards, tom lane

Re: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions

From
"David G. Johnston"
Date:
On Tue, Apr 5, 2022 at 9:08 PM Jian He <hejian.mark@gmail.com> wrote:

If the question is stupid, please tell me why. 

Not stupid, but the question as phrased is making an assumption about how the system works that isn't true.  Or, at least, I don't understand what you mean by "deduce it" and "query it"?  The fact those check constraints exist shows you PostgreSQL doesn't deduce it in order to prevent their creation.  There is also nothing provided to query existing constraints and sanity check them.  Nor is such a feature all that valuable - such constraints are seldom written and the expected cursory testing that should go along with such a thing, or even the fact that the system should probably not function while they exist, means that when they do get created they are quickly discovered.  For the redundant check constraint, it is not so easily discovered but neither does it seem like a big deal - so long as the relevant operator is cheap to execute.  That tends to be the case for check constraints.  When they are not, they usually are not easily figured out to be logically redundant either.

I wrote the following having mis-understood your question.  I will leave it because it may be informative, and is at least tangentially on-topic:

IIUC, queries look at statistics to make decisions.  They will also look at, I think, unique constraints at the table level and not null constraints at the column level.  Not sure about references - I think the unique and stats handles those sufficiently.

CREATE TABLE emp (test_check int check ( test_check >1 and test_check < 0 ));

alter table emp  VALIDATE CONSTRAINT emp_test_check_check;```

select * from pg_constraint where conname = 'emp_test_check_check';

Even with the above mentioned step, does postgresql know above check constraint condition always false.

Indirectly, it will know, using statistics, that either the table itself is empty or that the most common, and only, value for the column is null (null_frac = 1.0)


another similar question:
can postgresql deduce from
CREATE TABLE emp1 (test_check int check ( test_check >1 and test_check > 10 ));

to

CREATE TABLE emp1 (test_check int check ( test_check > 10 ));

I think only in the resulting statistics, most likely in this case the lowest histogram_bounds boundary should be 11.

David J.