Thread: NOVALIDATE in postgresql?
Hi
In Oracle I can use the NOVALIDATE for constraints... like this
ALTER TABLE employee ADD
CONSTRAINT emp_ck
CHECK (married IN ('Y','N')) NO VALIDATE;
When the table is already populated this will be faster. Can you do the same in Postgresql?
Thanks
Sharmila
2009/2/19 SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com>:
> Hi
> In Oracle I can use the NOVALIDATE for constraints... like this
> ALTER TABLE employee ADD
> CONSTRAINT emp_ck
> CHECK (married IN ('Y','N')) NO VALIDATE;
>
> When the table is already populated this will be faster. Can you do the same in Postgresql?
>
From manual:
"Adding a CHECK or NOT NULL constraint requires scanning the table to
verify that existing rows meet the constraint."
http://www.postgresql.org/docs/current/interactive/sql-altertable.html
Osvaldo
----- "SHARMILA JOTHIRAJAH" <sharmi_jo@yahoo.com> wrote:
> Hi
> In Oracle I can use the NOVALIDATE for constraints... like this
> ALTER TABLE employee ADD
> CONSTRAINT emp_ck
> CHECK (married IN ('Y','N')) NO VALIDATE;
>
> When the table is already populated this will be faster. Can you do
> the same in Postgresql?
> Thanks
> Sharmila
From the Oracle manual:
ENABLE NOVALIDATE means the constraint is checked for new or modified rows, but existing data may violate the
constraint.
So you are looking for an incomplete constraint?
Adrian Klaver
aklaver@comcast.net
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
On Feb 19, 2009, at 1:49 PM, Adrian Klaver wrote: > From the Oracle manual: > ENABLE NOVALIDATE means the constraint is checked for new or > modified rows, but existing data may violate the constraint. > > So you are looking for an incomplete constraint? More likely they want to add a constraint but can't afford the time it would take to scan the table while holding an exclusive lock. At least that's the situation we're facing at work. FWIW, I've been talking to Command Prompt about developing a fix for this, targeting inclusion in 8.5. I think Alvaro and I have come up with a reasonably plan, but there hasn't been time to present it to the community yet. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
On Friday 20 February 2009 7:57:32 pm decibel wrote: > On Feb 19, 2009, at 1:49 PM, Adrian Klaver wrote: > > From the Oracle manual: > > ENABLE NOVALIDATE means the constraint is checked for new or > > modified rows, but existing data may violate the constraint. > > > > So you are looking for an incomplete constraint? > > More likely they want to add a constraint but can't afford the time > it would take to scan the table while holding an exclusive lock. At > least that's the situation we're facing at work. I get it now, basically validate on demand, so the cost is spread out instead of incurred at the ALTER TABLE command. > > FWIW, I've been talking to Command Prompt about developing a fix for > this, targeting inclusion in 8.5. I think Alvaro and I have come up > with a reasonably plan, but there hasn't been time to present it to > the community yet. -- Adrian Klaver aklaver@comcast.net