Glide SQL keys

script.sql

DROP TABLE IF EXISTS keys;

CREATE TABLE   keys (
    key_name   VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY,

    context    VARCHAR(128) NOT NULL,

    position   INTEGER      NOT NULL,

    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    UNIQUE (context, position) DEFERRABLE
);

CREATE OR REPLACE FUNCTION glide_keys_position()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE keys
    SET position = position + 1
    WHERE
        context = NEW.context AND position >= NEW.position;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_glide_keys_position
BEFORE INSERT ON keys
FOR EACH ROW EXECUTE PROCEDURE glide_keys_position();


-- here a sample of data
INSERT INTO keys (key_name, context, position)
VALUES
('A.1', 'ctx_A', 0), 
('A.2', 'ctx_A', 1), 
('A.3', 'ctx_A', 2), 
('A.4', 'ctx_A', 3),
('B.1', 'ctx_B', 0), 
('B.2', 'ctx_B', 1), 
('B.3', 'ctx_B', 2), 
('B.4', 'ctx_B', 3);

-- UPDATE keys SET position = position + 1 WHERE context = 'ctx_A' AND position > 1;

INSERT INTO keys (key_name, context, position) VALUES ('A.10', 'ctx_A', 2);

BEGIN;
  SET CONSTRAINTS keys_context_position_key DEFERRED;

  UPDATE keys
  SET position = CASE key_name
    WHEN 'A.4' THEN (SELECT position FROM keys WHERE context='ctx_A' AND key_name = 'A.3')
    WHEN 'A.3' THEN (SELECT position FROM keys WHERE context='ctx_A' AND key_name = 'A.4')
  END
  WHERE key_name IN ('A.3', 'A.4');
COMMIT;

SELECT * FROM keys WHERE context='ctx_A' ORDER BY position;

output:

DROP TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 8
INSERT 0 1
BEGIN
SET CONSTRAINTS
UPDATE 2
COMMIT
 key_name | context | position |          created_at
----------+---------+----------+-------------------------------
 A.1      | ctx_A   |        0 | 2021-10-28 11:09:44.667153+00
 A.2      | ctx_A   |        1 | 2021-10-28 11:09:44.667153+00
 A.10     | ctx_A   |        2 | 2021-10-28 11:09:44.669278+00
 A.4      | ctx_A   |        3 | 2021-10-28 11:09:44.667153+00
 A.3      | ctx_A   |        4 | 2021-10-28 11:09:44.667153+00
(5 rows)