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

Answer 1

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.