Re: BUG #14596: False primary/unique key constraint violations

On Wed, Mar 22, 2017 at 5:30 AM, <rasmus(at)mindplay(dot)dk> wrote:

>
> CREATE TABLE public.test
> (
> name character varying(100),
> index integer,
> CONSTRAINT unique_index PRIMARY KEY (index)
> );
>
> And the following sample data:
> INSERT INTO "test" ("name", "index") VALUES ('A', 0);
> INSERT INTO "test" ("name", "index") VALUES ('B', 1);
> INSERT INTO "test" ("name", "index") VALUES ('C', 2);
>
> The following query will fail:
> UPDATE "test" SET "index" = "index" + 1 WHERE "index" >= 0;

Try the following with a deferred constraint:

CREATE TABLE public.test
(
name character varying(100),
index integer,
CONSTRAINT unique_index PRIMARY KEY (index) DEFERRABLE INITIALLY DEFERRED
);

This behaviour is documented at
https://www.postgresql.org/docs/current/static/sql-createtable.html