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 );
