Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE widget (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE  -- NOT DEFERRABLE
);
INSERT INTO widget VALUES
  (1, 1), (2, 2), (3, 3);

CREATE TABLE widget_deferrable (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE DEFERRABLE
);
INSERT INTO widget_deferrable VALUES
  (1, 1), (2, 2), (3, 3);

CREATE TABLE widget_deferred (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO widget_deferred VALUES
  (1, 1), (2, 2), (3, 3);

CREATE TABLE

INSERT 0 3

CREATE TABLE

INSERT 0 3

CREATE TABLE

INSERT 0 3

-- not deferrable; cheapest, but restrictive
-- needs workaround like
UPDATE widget
SET    ordinal = 0
WHERE  id = 3;

UPDATE widget
SET    ordinal = 3
WHERE  id = 1;

UPDATE widget
SET    ordinal = 1
WHERE  id = 3;

TABLE widget ORDER BY id;

UPDATE 1

UPDATE 1

UPDATE 1

id
ordinal

1
3

2
2

3
1

SELECT 3

-- deferrable; more expensive; additionally allows:
UPDATE widget_deferrable w
SET    ordinal = t.ord
FROM  (
   VALUES
     (1, 3)
   , (3, 1) 
   ) t(id, ord)
WHERE  t.id = w.id;

TABLE widget_deferrable ORDER BY id;

UPDATE 2

id
ordinal

1
3

2
2

3
1

SELECT 3

-- Or:
TRUNCATE widget_deferrable;  -- init
INSERT INTO widget_deferrable VALUES
  (1, 1), (2, 2), (3, 3);

WITH up1 AS (
   UPDATE widget_deferrable
   SET    ordinal = 1
   WHERE  id = 3
   )
UPDATE widget_deferrable
SET    ordinal = 3
WHERE  id = 1;

TABLE widget_deferrable ORDER BY id;

TRUNCATE TABLE

INSERT 0 3

UPDATE 1

id
ordinal

1
3

2
2

3
1

SELECT 3

-- deferred; (much) more expensive, yet; additionally allows:
BEGIN;
UPDATE widget_deferred
SET    ordinal = 1
WHERE  id = 3;

UPDATE widget_deferred
SET    ordinal = 3
WHERE  id = 1;
COMMIT;

TABLE widget_deferred ORDER BY id;

BEGIN

UPDATE 1

UPDATE 1

COMMIT

id
ordinal

1
3

2
2

3
1

SELECT 3