Hello. Thanks for the answer. I really want to avoid reading the whole table. It is too expensive, and with the proposed feature will be not needed. I think is much faster to forcefully add the check if you know the range of data.
What do you think? -- Catalin(ux) M. BOIE http://kernel.embedromix.ro ----- Reply message ----- From: "Jeff Davis" <pg...@j-davis.com> To: "Catalin(ux) M. BOIE" <ca...@embedromix.ro> Cc: <pgsql-general@postgresql.org> Subject: [GENERAL] Forcefully adding a CHECK constrained Date: Sat, May 26, 2012 20:48 On Tue, 2012-05-15 at 12:52 +0300, Catalin(ux) M. BOIE wrote: > The old_stats is so big that I cannot afford to add a check constraint. > But, I know that all values of the itime field are before 2012_04, so, > would be great if I could run something like: > > ALTER TABLE old_stats ADD CONSTRAINT xxx CHECK (itime < 2012_04_timestamp) > FORCE; > > I never looked at PostgreSQL sources, but the commit > Enable CHECK constraints to be declared NOT VALID > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=897795240cfaaed724af2f53ed2c50c9862f951f > inspired me to dive. > Is PostgreSQL's team willing to accept such a feature? It looks like you already found the answer! Create the constraint using NOT VALID, and then sometime later (when you can afford the full scan) do a VALIDATE CONSTRAINT. Unfortunately, this is only available in 9.2, which is still in beta. http://www.postgresql.org/docs/9.2/static/sql-altertable.html CHECK constraints don't use indexes, so CREATE INDEX CONCURRENTLY doesn't help you. Regards, Jeff Davis
