albttx's questions

Eu tenho uma tabela que requer armazenar meu item em uma posição específica, e o usuário pode "mover" as posições dos itens para "dar espaço" para o novo item.

Aqui está a aparência da mesa

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

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

Eu gostaria de poder inserir uma chave na posição 1 ou mover a chave positionde uma ( UNIQUE), e isso incrementa automaticamente o positioninteiro em 1 para cada valor maior que o novo INSERT.

Aqui está o que eu tentei até agora

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

ERRO: valor de chave duplicado viola restrição exclusiva "keys_context_position_key" DETALHE: A chave (context, "position")=(ctx_A, 3) já existe.

Mas não está funcionando.

EDITAR

Estou usando uma imagem do Dockerpostgres:12.7

Eu descobri que usar UNIQUE (context, position) DEFERRABLEme permite executar

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

Mas algo como

BEGIN;
  UPDATE keys SET position=2 WHERE context='ctx_A' AND key_name='A.4';
  UPDATE keys SET position=3 WHERE context='ctx_A' AND key_name='A.3';
COMMIT;

ainda não está funcionando!