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

On Tue, Apr 5, 2022 at 9:08 PM Jian He <hejian(dot)mark(at)gmail(dot)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.