NOVALIDATE in postgresql?

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

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