No PostgreSQL 15 ou posterior, use uma restrição UNIQUE NULLS NOT DISTINCT declarada como DEFERRABLE e, em seguida, adie sua verificação dentro da transação de reordenação. Isso permite que as linhas de artigos troquem de posição, mantendo uma ordem final única, inclusive para linhas cujo pai é NULL. Sintaxe de restrições do PostgreSQL

Este tutorial destina-se a um desenvolvedor que trabalha no Terminal do Mac e precisa reordenar linhas sintéticas de artigos dentro de um grupo de pais. Inclui uma demonstração em SQL, um programa completo com node-postgres que valida a ordem solicitada e uma alternativa para o PostgreSQL 14. Reatribuir artigos a outros pais está fora do escopo deste exemplo.

Por que um pai que pode ser nulo complica a unicidade das posições?

Suponha que cada artigo tenha um id, um parent_id que pode ser nulo e um position inteiro positivo. Artigos com o mesmo pai formam um grupo ordenado de irmãos. Neste modelo, todos os artigos com pai nulo pertencem a um único grupo raiz.

Um UNIQUE (parent_id, position) simples não impõe essa regra para o grupo raiz. Normalmente, o PostgreSQL trata valores nulos como distintos para fins de unicidade, então dois artigos raiz podem ocupar a posição 1 ao mesmo tempo. Adicionar NULLS NOT DISTINCT faz com que esses valores nulos de pai sejam considerados iguais ao verificar a chave composta. Pais diferentes e não nulos ainda podem ter, cada um, sua própria posição 1. PostgreSQL: restrições UNIQUE

A reordenação cria um problema distinto de temporização. Se o artigo 1 ocupa a posição 1 e o artigo 2 ocupa a posição 2, mover o artigo 2 para a posição 1 duplica temporariamente a posição do artigo 1. Uma restrição UNIQUE não adiável verifica cada linha inserida ou modificada imediatamente. Uma restrição adiável pode esperar até o fim da instrução ou, quando adiada explicitamente, até o fim da transação. PostgreSQL: CREATE TABLE

Em uma reordenação implementada com várias atualizações, as duas propriedades são importantes: a unicidade que considera valores nulos define os grupos corretos, e a verificação adiada permite colisões temporárias entre as atualizações.

Qual definição você deve usar na sua versão do PostgreSQL?

O PostgreSQL 15 introduziu NULLS NOT DISTINCT para restrições e índices UNIQUE. Os exemplos principais abaixo exigem a versão 15 ou posterior do servidor; o PostgreSQL 14 precisa da alternativa apresentada mais adiante. Verifique a versão do servidor conectado em vez de inferi-la pela versão do cliente de linha de comando instalado. Notas de versão do PostgreSQL 15

Situação Abordagem compatível
PostgreSQL 15 ou posterior, nova tabela Restrição UNIQUE NULLS NOT DISTINCT nomeada com DEFERRABLE INITIALLY IMMEDIATE
Restrição UNIQUE comum já existente Substitua sua definição; ALTER CONSTRAINT não pode alterar a adiabilidade de uma restrição UNIQUE
Índice UNIQUE de expressão ou parcial já existente Substitua o mecanismo de imposição; ele não pode ser associado como uma restrição UNIQUE comum
PostgreSQL 14 Use colunas geradas para agrupar, com uma restrição UNIQUE adiável

Os limites para objetos existentes estão documentados em ALTER TABLE: alterar atributos de restrições dessa forma se aplica apenas a chaves estrangeiras, e um índice associado como restrição UNIQUE não pode conter expressões nem ser parcial. Um CREATE UNIQUE INDEX independente também não tem uma cláusula DEFERRABLE. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

Como trocar a posição de dois artigos raiz em SQL?

Os pré-requisitos são um banco de dados PostgreSQL 15 ou posterior existente e acessível localmente e uma conexão psql funcional. Abra o Terminal do Mac e inicie o cliente usando as configurações de conexão já definidas. O comando a seguir ignora as personalizações de inicialização e interrompe o processamento do script em caso de erro. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Cole a demonstração SQL completa abaixo nessa sessão. Ela cria uma tabela temporária contendo apenas artigos sintéticos; tabelas temporárias desaparecem quando a sessão termina. O nome da tabela precisa estar livre nessa sessão. PostgreSQL: tabelas temporárias

SHOW server_version;

CREATE TEMP TABLE article_order_demo (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    CONSTRAINT article_order_demo_parent_position_key
        UNIQUE NULLS NOT DISTINCT (parent_id, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo (id, parent_id, title, position)
VALUES
    (1, NULL, 'Article A', 1),
    (2, NULL, 'Article B', 2),
    (3, NULL, 'Article C', 3),
    (4, 1, 'Article A: Overview', 1),
    (5, 1, 'Article A: Examples', 2);

BEGIN;
SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED;

UPDATE article_order_demo
SET position = 1
WHERE id = 2 AND parent_id IS NULL;

UPDATE article_order_demo
SET position = 2
WHERE id = 1 AND parent_id IS NULL;

SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo
WHERE parent_id IS NULL
ORDER BY position;

Após a primeira atualização, a transação contém temporariamente duas linhas raiz na posição 1. Depois da segunda atualização, as posições voltam a ser únicas. Mudar a restrição de volta para IMMEDIATE verifica nesse momento as alterações pendentes. Se ainda houver uma duplicata, esse comando falha; caso contrário, a transação pode ser confirmada. O adiamento altera o momento em que a validação ocorre. PostgreSQL: SET CONSTRAINTS

A ordem final esperada das raízes é:

Posição ID do artigo Título
1 2 Artigo B
2 1 Artigo A
3 3 Artigo C

Os artigos filhos mantêm suas posições porque nenhuma das atualizações os seleciona. Se ocorrer um erro em uma transação interativa, execute ROLLBACK; antes de tentar novamente. Uma transação agrupa as instruções relacionadas até COMMIT ou ROLLBACK. PostgreSQL: BEGIN

Como reordenar um grupo completo de irmãos com node-postgres?

Use um único cliente obtido do pool durante toda a transação. O node-postgres exige isso explicitamente: chamadas separadas a pool.query() não servem para uma transação porque podem ser executadas em conexões diferentes. node-postgres: transações

O programa abaixo aceita uma lista ordenada completa de IDs de irmãos. Ele segue uma regra adicional simples: a lista fornecida precisa ser uma permutação do conjunto atual de irmãos. Rejeitar duplicatas, omissões e IDs de outros grupos evita uma reordenação parcial que pareça ter sido concluída com sucesso.

Em um projeto Node.js local, instale pg, salve o programa completo a seguir como reorder.cjs e execute-o. O programa pressupõe que as configurações de conexão local já estejam disponíveis por meio de variáveis de ambiente do PostgreSQL. O node-postgres aceita as mesmas variáveis de ambiente de conexão que psql. node-postgres: conexão

npm install pg
node reorder.cjs

const { Pool } = require('pg');

function validateOrder(ids, rows) {
  if (!Array.isArray(ids) || !ids.every(Number.isSafeInteger) ||
      new Set(ids).size !== ids.length) {
    throw new Error('Order must contain distinct integer IDs');
  }
  const existing = new Set(rows.map(row => row.id));
  if (rows.length !== ids.length || ids.some(id => !existing.has(id))) {
    throw new Error('Order must include every sibling exactly once');
  }
}

async function main() {
  const pool = new Pool();
  let client;
  try {
    client = await pool.connect();
    await client.query(`
      CREATE TEMP TABLE article_order_demo (
        id integer PRIMARY KEY,
        parent_id integer REFERENCES article_order_demo(id),
        title text NOT NULL,
        position integer NOT NULL CHECK (position > 0),
        CONSTRAINT article_order_demo_parent_position_key
          UNIQUE NULLS NOT DISTINCT (parent_id, position)
          DEFERRABLE INITIALLY IMMEDIATE
      )
    `);
    await client.query(`
      INSERT INTO article_order_demo (id, parent_id, title, position)
      VALUES (1, NULL, 'Article A', 1),
             (2, NULL, 'Article B', 2),
             (3, NULL, 'Article C', 3),
             (4, 1, 'Article A: Overview', 1),
             (5, 1, 'Article A: Examples', 2)
    `);

    const parentId = null;
    const orderedIds = [2, 1, 3];
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    try {
      await client.query(
        'LOCK TABLE article_order_demo IN SHARE ROW EXCLUSIVE MODE'
      );
      const siblings = await client.query(
        `SELECT id FROM article_order_demo
         WHERE parent_id IS NOT DISTINCT FROM $1::integer`,
        [parentId]
      );
      validateOrder(orderedIds, siblings.rows);
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED'
      );
      for (const [index, id] of orderedIds.entries()) {
        const result = await client.query(
          `UPDATE article_order_demo SET position = $1
           WHERE id = $2
             AND parent_id IS NOT DISTINCT FROM $3::integer`,
          [index + 1, id, parentId]
        );
        if (result.rowCount !== 1) {
          throw new Error('Expected exactly one updated article');
        }
      }
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE'
      );
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    }

    const result = await client.query(
      `SELECT id, title, position FROM article_order_demo
       WHERE parent_id IS NOT DISTINCT FROM $1::integer
       ORDER BY position`,
      [parentId]
    );
    console.table(result.rows);
  } finally {
    if (client) client.release();
    await pool.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

IS NOT DISTINCT FROM faz com que o filtro de pai funcione tanto para valores nulos quanto para valores não nulos. Uma comparação comum com um parâmetro nulo não seleciona as raízes. Para reordenar os artigos filhos do exemplo, altere parentId para 1 e orderedIds para [5, 4]. PostgreSQL: funções e operadores de comparação

Os marcadores de posição no formato $1 transportam valores separadamente do texto SQL. A conversão explícita para inteiro também informa o tipo pretendido quando o parâmetro de pai é nulo. node-postgres: consultas

Por que adquirir o bloqueio antes de ler os irmãos?

A demonstração temporária não tem sessões concorrentes. O bloqueio de tabela ilustra uma abordagem deliberadamente abrangente para uma tabela pequena e compartilhada: SHARE ROW EXCLUSIVE entra em conflito com gravações normais na tabela e com bloqueios do mesmo tipo, mas permite leituras comuns. Mantê-lo durante toda a transação estabiliza a composição do grupo de irmãos durante a validação e as atualizações. Ele também bloqueia gravações em grupos de outros pais; avalie essa desvantagem antes de usá-lo em uma tabela movimentada. PostgreSQL: bloqueio explícito

O programa seleciona explicitamente o nível READ COMMITTED. Nesse nível de isolamento, a consulta dos irmãos vê alterações confirmadas até o início da própria consulta, incluindo alterações confirmadas antes do fim de uma espera por bloqueio anterior. PostgreSQL: BEGIN, PostgreSQL: isolamento de transações

A validação em JavaScript foi verificada com uma permutação válida e com IDs duplicados, ausentes e de outro grupo. Essas verificações determinísticas foram aprovadas. Os exemplos de banco de dados foram conferidos com a documentação, mas não foram executados em um servidor PostgreSQL no ambiente de pesquisa.

Qual é a alternativa compatível com o PostgreSQL 14?

No PostgreSQL 14, materialize a expressão de agrupamento em colunas geradas e aplique uma restrição UNIQUE adiável a essas colunas. Colunas geradas armazenadas são calculadas quando as linhas são gravadas e não podem ser substituídas por valores comuns. PostgreSQL 14: colunas geradas

Este exemplo SQL independente usa dois valores gerados para representar o grupo do pai. O valor booleano distingue as raízes de um ID de pai real igual a 0, então o valor substituto COALESCE não exige reservar um ID.

CREATE TEMP TABLE article_order_demo14 (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo14(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    parent_is_root boolean
        GENERATED ALWAYS AS (parent_id IS NULL) STORED,
    parent_group integer
        GENERATED ALWAYS AS (COALESCE(parent_id, 0)) STORED,
    CONSTRAINT article_order_demo14_parent_position_key
        UNIQUE (parent_is_root, parent_group, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo14 (id, parent_id, title, position)
VALUES (1, NULL, 'Article A', 1),
       (2, NULL, 'Article B', 2);

BEGIN;
SET CONSTRAINTS article_order_demo14_parent_position_key DEFERRED;
UPDATE article_order_demo14 SET position = 1 WHERE id = 2;
UPDATE article_order_demo14 SET position = 2 WHERE id = 1;
SET CONSTRAINTS article_order_demo14_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo14
WHERE parent_id IS NULL
ORDER BY position;

Para uma raiz, os valores gerados de agrupamento são (true, 0); para o pai 0, são (false, 0). Cada pai não nulo mantém seu próprio valor inteiro. Assim, a restrição expressa o agrupamento pretendido sem depender do tratamento de valores nulos do PostgreSQL 15. O PostgreSQL 14 permite restrições UNIQUE adiáveis em colunas nomeadas. PostgreSQL 14: CREATE TABLE

O que você deve verificar antes de adaptar uma tabela existente?

Um índice de expressão sobre COALESCE(parent_id, 0) pode impor unicidade baseada em expressão, mas não pode simplesmente ser tornado adiável nem associado como uma restrição UNIQUE comum. Um índice parcial para linhas raiz tem a mesma limitação de associação. Manter qualquer um desses objetos de imposição imediata pode continuar impedindo uma troca após a adição de uma restrição adiável. PostgreSQL: índices em expressões, PostgreSQL: ALTER TABLE

Antes de substituir uma definição existente, verifique pares duplicados de pai e posição, especialmente raízes que antes eram permitidas pela unicidade nula comum. Ao adicionar uma restrição UNIQUE, as linhas existentes são validadas; não há opção NOT VALID para restrições UNIQUE. Resolva deliberadamente as posições ambíguas antes de tentar a alteração. PostgreSQL: ALTER TABLE

Mantenha separado o compromisso da restrição da regra de ordenação da aplicação. A unicidade por si só permite lacunas, como as posições 1, 4 e 9. O programa de lista completa atribui posições consecutivas, começando em 1. É a verificação de composição que garante a participação de todos os irmãos.

Para essa tarefa, escolha a restrição que representa grupos de pais que podem ser nulos, adie sua verificação antes da primeira atualização que causaria uma colisão, valide o conjunto completo de irmãos solicitado e conclua todas as atualizações em um único cliente antes de confirmar a transação. Essa combinação permite reordenar raízes e filhos, mantendo uma posição final única para cada irmão.

Fontes