When does Postgres check unique constraints?

Question

I have a column sort_order with a unique constraint on it. The following SQL fails on Postgres 9.5:

UPDATE test
SET sort_order = sort_order + 1;

-- [23505] ERROR: duplicate key value violates unique constraint "test_sort_order_key"
--   Detail: Key (sort_order)=(2) already exists.

Clearly, if the sort_order values were unique before the update, they will still be unique after the update. Why is this?

The same statement works fine on Oracle and MS SQL, but also fails on MySQL and SQLite.

Here's the complete setup code for a SQL fiddle:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
  val        TEXT,
  sort_order INTEGER NOT NULL UNIQUE
);

INSERT INTO test
VALUES ('A', 1), ('B', 2);

blubb · Accepted Answer · 2021-03-17 14:13:47Z

11

Postgres decides to check constraints of type IMMEDIATELY at a different time than proposed in the SQL standard.

Specifically, the documentation for SET CONSTRAINTS states (emphasis mine):

NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.

Postgres chooses to execute this query using a plan that results in a temporary collision for sort_order and IMMEDIATELY fails. Note that means that for the same schema and the same data, the same query may work or fail depending on the execution plan.

You'll have to make the constraint DEFERRABLE or DEFERRABLE INITIALLY DEFERRED, which delays verification of the constraint until the end of the transaction or up to the point where a statement SET CONSTRAINTS ... IMMEDIATE is executed.

Addendum from @HansGinzel's comment:

for COPY, it seems, that (even IMMEDIATE) constraints are tested after all data are COPYied.

edited Mar 17, 2021 at 14:13

answered Mar 29, 2018 at 13:43

blubb

10k3 gold badges45 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ypercubeᵀᴹ Over a year ago

Postgres doesn't have a "different idea about what IMMEDIATELY means". It is a documented difference from the standard, in implementation of checking UNIQUE constraints.

blubb Over a year ago

@ypercubeᵀᴹ: Thanks, I corrected the wording. Do you happen to know where in the SQL standard this is specified to make the answer even more complete? Unfortunately, I don't have a copy of it. Feel free to edit my answer :-)

Hans Ginzel Over a year ago

For COPY, it seems, that (even IMMEDIATE) constraints are tested after all data are COPYied.

blubb Over a year ago

@HansGinzel Thanks, that's an interesting fact! I'll include it in the answer to make it a bit more visible.

Collectives™ on Stack Overflow

When does Postgres check unique constraints?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related