On 1 June 2011 23:47, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> Here's a complete patch with all this stuff, plus doc additions and
> simple regression tests for the new ALTER DOMAIN commands.
>
> Enable CHECK constraints to be declared NOT VALID
>
> This means that they can initially be added to a large existing table
> without checking its initial contents, but new tuples must comply to
> them; a separate pass invoked by ALTER TABLE / VALIDATE can verify
> existing data and ensure it complies with the constraint, at which point
> it is marked validated and becomes a normal part of the table ecosystem.
>
> This patch also enables domains to have unvalidated CHECK constraints
> attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT
> VALID, which can later be validated with ALTER DOMAIN / VALIDATE
> CONSTRAINT.
Is this expected?
postgres=# CREATE TABLE a (num INT);
CREATE TABLE
postgres=# INSERT INTO a (num) VALUES (90);
INSERT 0 1
postgres=# ALTER TABLE a ADD CONSTRAINT meow CHECK (num < 20) NOT VALID;
ALTER TABLE
postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \q
postgresql thom$ pg_dump -f /tmp/test.sql postgres
postgresql thom$ psql test < /tmp/test.sql
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
ERROR: new row for relation "a" violates check constraint "meow"
CONTEXT: COPY a, line 1: "90"
STATEMENT: COPY a (num) FROM stdin;
ERROR: new row for relation "a" violates check constraint "meow"
CONTEXT: COPY a, line 1: "90"
REVOKE
REVOKE
GRANT
GRANT
Shouldn't the constraint be dumped as not valid too??
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
