Jian He The context here is constraints for partitioning as suggested in documentation https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE
An example constraint from the documentation: ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); If logdate is indexed, then this constraint can be manually validated very quickly using a SELECT that will take advantage of the index SELECT 1 FROM measurement_y2008m02 WHERE logdate < DATE '2008-02-01' OR logdate >= DATE '2008-03-01' LIMIT 1 If the constraint is valid the query will return quickly with no rows, if any rows violate the constraint it will also return very quickly but return with a single row with column value: 1. I guess that validating constraints doesn't invoke the query planner, or otherwise the conversion is too complex for the query planner. The conversion being: - from: NOT (logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01') - to: logdate < DATE '2008-02-01' OR logdate >= DATE '2008-03-01' Hope that clarifies it. On Wed, 20 Nov 2024 at 09:45, jian he <jian.universal...@gmail.com> wrote: > On Fri, Nov 15, 2024 at 4:38 PM Philip Couling <coul...@gmail.com> wrote: > > > > Is there a solid reason why adding a check constraint does not use > existing indexes for validation. > > > > can you give an sql example (except not-null) > where indexes can be used for check constraint validation? > i am not sure I understand it correctly. >
