public inbox for [email protected]help / color / mirror / Atom feed
Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions 3+ messages / 3 participants [nested] [flat]
* Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions @ 2022-04-06 04:07 Jian He <[email protected]> 2022-04-06 04:10 ` Re: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions Tom Lane <[email protected]> 2022-04-06 04:33 ` Re: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions David G. Johnston <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Jian He @ 2022-04-06 04:07 UTC (permalink / raw) To: pgsql-sql <[email protected]> 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 )); ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions 2022-04-06 04:07 Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions Jian He <[email protected]> @ 2022-04-06 04:10 ` Tom Lane <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Tom Lane @ 2022-04-06 04:10 UTC (permalink / raw) To: Jian He <[email protected]>; +Cc: pgsql-sql <[email protected]> Jian He <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions 2022-04-06 04:07 Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions Jian He <[email protected]> @ 2022-04-06 04:33 ` David G. Johnston <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: David G. Johnston @ 2022-04-06 04:33 UTC (permalink / raw) To: Jian He <[email protected]>; +Cc: pgsql-sql <[email protected]> On Tue, Apr 5, 2022 at 9:08 PM Jian He <[email protected]> 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-04-06 04:33 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-04-06 04:07 Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions Jian He <[email protected]> 2022-04-06 04:10 ` Tom Lane <[email protected]> 2022-04-06 04:33 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox