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

From: Jian He <[email-masked]>
Date: Wed, 6 Apr 2022 09:37:55 +0530

If the question is stupid, please tell me why.

postgresql - How to query check the constraint is valid or not - Stack
Overflow
<https://stackoverflow.com/questions/71746611/how-to-query-check-the-constraint-is-valid-or-not>
Postgres 14 | db<>fiddle (dbfiddle.uk)
<https://dbfiddle.uk/?rdbms=postgres_14&fiddle=8157a4c1823635980019f3bdb1fa6835>

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 06 Apr 2022 00:10:25 -0400

> constraint condition always false.

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

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 5 Apr 2022 21:33:47 -0700

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

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

> CREATE TABLE emp1 (test_check int check ( test_check >1 and test_check >
> 10 ));
>
> to
>
I think only in the resulting statistics, most likely in this case the
lowest histogram_bounds boundary should be 11.

David J.