On PostgreSQL 15 or later, use a UNIQUE NULLS NOT DISTINCT constraint declared DEFERRABLE, then defer it inside the reorder transaction. This lets article rows exchange positions while enforcing a unique final order, including rows whose parent is NULL. PostgreSQL constraint syntax

This tutorial is for a developer working in Mac Terminal who needs to reorder synthetic article rows within one parent group. It includes a SQL demonstration, a complete node-postgres program that validates the requested order, and a PostgreSQL 14 alternative. Reparenting articles is outside this example’s scope.

Why does a nullable parent complicate position uniqueness?

Assume each article has an id, a nullable parent_id, and a positive integer position. Articles with the same parent form one ordered sibling group. In this model, all articles with a null parent belong to a single root group.

A plain UNIQUE (parent_id, position) does not enforce that root-group rule. PostgreSQL normally treats nulls as distinct for uniqueness, so two root articles can both occupy position 1. Adding NULLS NOT DISTINCT makes those null parent values compare alike when checking the composite key. Different non-null parents can still each have their own position 1. PostgreSQL: Unique Constraints

Reordering creates a separate timing problem. If article 1 occupies position 1 and article 2 occupies position 2, moving article 2 to position 1 temporarily duplicates article 1’s position. A non-deferrable unique constraint checks each inserted or modified row immediately. A deferrable constraint can wait until the statement ends, or until the transaction finishes when explicitly deferred. PostgreSQL: CREATE TABLE

For a reorder implemented as several updates, both properties matter: null-aware uniqueness defines the correct groups, and deferred checking permits temporary collisions between updates.

Which definition should you use on your PostgreSQL version?

PostgreSQL 15 introduced NULLS NOT DISTINCT for unique constraints and indexes. The primary examples below require server version 15 or later; PostgreSQL 14 needs the alternative shown later. Check the connected server rather than inferring its version from the installed command-line client. PostgreSQL 15 release notes

Situation Supported approach
PostgreSQL 15 or later, new table Named UNIQUE NULLS NOT DISTINCT constraint with DEFERRABLE INITIALLY IMMEDIATE
Existing ordinary unique constraint Replace its definition; ALTER CONSTRAINT cannot change a unique constraint’s deferrability
Existing expression or partial unique index Replace the enforcement design; it cannot be attached as an ordinary unique constraint
PostgreSQL 14 Use generated grouping columns with a deferrable unique constraint

The existing-object boundaries are documented in ALTER TABLE: altering constraint attributes this way is limited to foreign keys, and an index attached as a unique constraint cannot contain expressions or be partial. A standalone CREATE UNIQUE INDEX also has no DEFERRABLE clause. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

How do you swap two root articles in SQL?

Prerequisites are an existing local PostgreSQL 15-or-later database and a working psql connection. Open Mac Terminal and start the client using your configured connection settings. The following command skips startup customizations and stops script processing on an error. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Paste the complete SQL demonstration below into that session. It creates a temporary table containing only synthetic articles; temporary tables disappear when the session ends. The table name must be unused in that session. PostgreSQL: Temporary Tables

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;

After the first update, the transaction temporarily contains two roots at position 1. After the second update, their positions are unique again. Switching the constraint back to IMMEDIATE checks outstanding changes at that point. If a duplicate remains, that command fails; otherwise, the transaction can commit. Deferral changes when validation happens. PostgreSQL: SET CONSTRAINTS

The expected final root order is:

Position Article ID Title
1 2 Article B
2 1 Article A
3 3 Article C

The child articles retain their positions because neither update selects them. If an interactive transaction encounters an error, issue ROLLBACK; before retrying. A transaction groups the related statements until COMMIT or ROLLBACK. PostgreSQL: BEGIN

How do you reorder a complete sibling group with node-postgres?

Use one checked-out client for the entire transaction. node-postgres explicitly requires this: separate pool.query() calls are unsuitable for a transaction because they can run on different connections. node-postgres: Transactions

The program below accepts a complete ordered list of sibling IDs. Its additional decision rule is simple: the supplied list must be a permutation of the current sibling set. Rejecting duplicates, omissions, and IDs from other groups prevents an apparently successful partial reorder.

In a local Node.js project, install pg, save the following complete program as reorder.cjs, and run it. The program assumes your existing local connection settings are available through PostgreSQL environment variables. node-postgres supports the same connection environment variables as psql. node-postgres: Connecting

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 makes the parent filter work for both null and non-null values. Ordinary equality with a null parameter does not select the roots. To reorder the sample children instead, change parentId to 1 and orderedIds to [5, 4]. PostgreSQL: Comparison Functions and Operators

The $1-style placeholders carry values separately from the SQL text. The explicit integer cast also states the intended type when the parent parameter is null. node-postgres: Queries

Why acquire the lock before reading siblings?

The temporary demonstration has no competing sessions. The table lock illustrates a deliberately broad approach for a small shared table: SHARE ROW EXCLUSIVE conflicts with normal table writes and with itself, while permitting ordinary reads. Holding it through the transaction keeps sibling membership stable during validation and updates. It also blocks writes to unrelated parent groups, so assess that tradeoff before using it on a busy table. PostgreSQL: Explicit Locking

The program explicitly selects READ COMMITTED. Under that isolation level, the sibling query sees committed changes as of that query’s start, including changes committed before a preceding lock wait finishes. PostgreSQL: BEGIN, PostgreSQL: Transaction Isolation

The JavaScript validation was checked with a valid permutation and with duplicate, missing, and foreign-group IDs. Those deterministic checks passed. The database examples were inspected against the documentation but were not executed against PostgreSQL in the research environment.

What is the supported alternative on PostgreSQL 14?

On PostgreSQL 14, materialize the grouping expression as generated columns, then apply a deferrable unique constraint to those columns. Stored generated columns are computed when rows are written and cannot be overridden with ordinary values. PostgreSQL 14: Generated Columns

This standalone SQL example uses two generated values to represent the parent group. The Boolean distinguishes roots from a genuine parent ID of 0, so the COALESCE fallback does not require reserving an 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;

For a root, the generated grouping values are (true, 0); for parent 0, they are (false, 0). Every non-null parent retains its own integer value. The constraint therefore expresses the intended grouping without depending on PostgreSQL 15’s null treatment. PostgreSQL 14 supports deferrable unique constraints on named columns. PostgreSQL 14: CREATE TABLE

What should you check before adapting an existing table?

An expression index on COALESCE(parent_id, 0) can enforce expression-based uniqueness, but it cannot simply be made deferrable or attached as an ordinary unique constraint. A partial index for root rows has the same attachment limitation. Leaving either immediate enforcement object in place can still prevent a swap after adding a deferrable constraint. PostgreSQL: Indexes on Expressions, PostgreSQL: ALTER TABLE

Before replacing an existing definition, inspect duplicate parent-position pairs, especially roots previously allowed by ordinary nullable uniqueness. Adding a unique constraint validates existing rows; NOT VALID is unavailable for unique constraints. Resolve ambiguous positions deliberately before attempting the change. PostgreSQL: ALTER TABLE

Keep the constraint’s promise separate from the application’s ordering rule. Uniqueness alone permits gaps such as positions 1, 4, and 9. The complete-list program deliberately assigns consecutive positions starting at 1. Its membership check is what ensures every sibling participates.

For this task, choose the constraint that represents nullable parent groups, defer it before the first colliding update, validate the complete requested sibling set, and finish all updates on one client before committing. That combination supports root and child reorders while retaining a unique final position for every sibling.

Sources