Re: [GENERAL] Forcefully adding a CHECK constrained

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" <pgsql(at)j-davis(dot)com>
To: "Catalin(ux) M. BOIE" <catab(at)embedromix(dot)ro>
Cc: <pgsql-general(at)postgresql(dot)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