Re: creating CHECK constraints as NOT VALID

On 1 June 2011 23:47, Alvaro Herrera <alvherre(at)commandprompt(dot)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