Обсуждение: Check whether a NOT NULL check constraint has been validated
Hello,
I have several NOT NULL check constraints in my schema that were originally added as NOT VALID. I would like to determine whether they have all since been validated, or not.
I thought maybe pg_constraint.convalidated might help, but it seems like the version of Postgres I'm using (16) doesn't store not null constraints in that table. And even in later versions, maybe it doesn't hold the information I need.
Is there another way to discover this?
This communication and any attachments may contain confidential information and are intended to be viewed only by the intended recipients. If you have received this message in error, please notify the sender immediately by replying to the original message and then delete all copies of the email from your systems.
On 11/30/25 16:59, Stuart Campbell wrote: > Hello, > > I have several NOT NULL check constraints in my schema that were > originally added as NOT VALID. I would like to determine whether they > have all since been validated, or not. > > I thought maybe pg_constraint.convalidated might help, but it seems like > the version of Postgres I'm using (16) doesn't store not null > constraints in that table. And even in later versions, maybe it doesn't > hold the information I need. > > Is there another way to discover this? 1) Select count(*) from <table> where <col> IS NULL; 2) From here https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-NOTES ALTER TABLE <table> VALIDATE CONSTRAINT <constraint_name>; As described: ". Once the constraint is in place, no new violations can be inserted, and the existing problems can be corrected at leisure until VALIDATE CONSTRAINT finally succeeds." > > This communication and any attachments may contain confidential > information and are intended to be viewed only by the intended > recipients. If you have received this message in error, please notify > the sender immediately by replying to the original message and then > delete all copies of the email from your systems. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/30/25 19:07, Adrian Klaver wrote: > On 11/30/25 16:59, Stuart Campbell wrote: >> Hello, >> >> I have several NOT NULL check constraints in my schema that were >> originally added as NOT VALID. I would like to determine whether they >> have all since been validated, or not. >> >> I thought maybe pg_constraint.convalidated might help, but it seems >> like the version of Postgres I'm using (16) doesn't store not null >> constraints in that table. And even in later versions, maybe it >> doesn't hold the information I need. >> >> Is there another way to discover this? > > 1) Select count(*) from <table> where <col> IS NULL; > > 2) From here > > https://www.postgresql.org/docs/current/sql-altertable.html#SQL- > ALTERTABLE-NOTES > > ALTER TABLE <table> VALIDATE CONSTRAINT <constraint_name>; > > As described: > > ". Once the constraint is in place, no new violations can be inserted, > and the existing problems can be corrected at leisure until VALIDATE > CONSTRAINT finally succeeds." Realized I was looking at current docs and I should be looking at your version From the 16 version of the docs: "This form adds a new constraint to a table using the same constraint syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints." Now I am not sure what you did. Are you saying you used a CHECK constraint to check for NULL values? If so then pg_constraint.convalidated would work. If not are you sure you are on Postgres 16? > >> >> This communication and any attachments may contain confidential >> information and are intended to be viewed only by the intended >> recipients. If you have received this message in error, please notify >> the sender immediately by replying to the original message and then >> delete all copies of the email from your systems. >> >> > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian,
Now I am not sure what you did.
Are you saying you used a CHECK constraint to check for NULL values?
That's right. Something like:
ALTER TABLE foo ADD CONSTRAINT bar_not_null CHECK (bar IS NOT NULL) NOT VALID
If so then pg_constraint.convalidated would work.
You're right. I didn't immediately find what I was looking for, and when I looked at the docs for pg_constraints, I guess I confused myself with this comment:
> Not-null constraints are represented in the
pg_attribute catalog, not here.This communication and any attachments may contain confidential information and are intended to be viewed only by the intended recipients. If you have received this message in error, please notify the sender immediately by replying to the original message and then delete all copies of the email from your systems.