Pada PostgreSQL 15 atau yang lebih baru, gunakan batasan UNIQUE NULLS NOT DISTINCT yang dideklarasikan DEFERRABLE, lalu tunda pemeriksaannya di dalam transaksi pengurutan ulang. Ini memungkinkan baris artikel saling bertukar posisi sekaligus menegakkan urutan akhir yang unik, termasuk untuk baris dengan induk NULL. Sintaks batasan PostgreSQL

Tutorial ini ditujukan bagi pengembang yang bekerja di Terminal Mac dan perlu mengurutkan ulang baris artikel sintetis dalam satu grup induk. Tutorial ini mencakup demonstrasi SQL, program node-postgres lengkap yang memvalidasi urutan yang diminta, dan alternatif untuk PostgreSQL 14. Memindahkan artikel ke induk lain tidak termasuk dalam cakupan contoh ini.

Mengapa induk yang dapat bernilai NULL mempersulit keunikan posisi?

Anggap setiap artikel memiliki id, parent_id yang dapat bernilai null, dan position berupa bilangan bulat positif. Artikel dengan induk yang sama membentuk satu grup saudara yang terurut. Dalam model ini, semua artikel dengan induk null termasuk dalam satu grup akar.

UNIQUE (parent_id, position) biasa tidak menegakkan aturan grup akar tersebut. PostgreSQL biasanya menganggap nilai null berbeda untuk keperluan keunikan, sehingga dua artikel akar dapat menempati posisi 1 secara bersamaan. Menambahkan NULLS NOT DISTINCT membuat nilai induk null dianggap sama saat kunci gabungan diperiksa. Induk non-null yang berbeda tetap dapat memiliki posisi 1 masing-masing. PostgreSQL: Batasan Unik

Pengurutan ulang menimbulkan masalah waktu yang terpisah. Jika artikel 1 menempati posisi 1 dan artikel 2 menempati posisi 2, memindahkan artikel 2 ke posisi 1 akan menduplikasi posisi artikel 1 untuk sementara. Batasan unik yang tidak dapat ditunda memeriksa setiap baris yang disisipkan atau diubah segera. Batasan yang dapat ditunda dapat menunggu hingga pernyataan berakhir, atau hingga transaksi selesai jika secara eksplisit ditunda. PostgreSQL: CREATE TABLE

Untuk pengurutan ulang yang diterapkan sebagai beberapa pembaruan, kedua sifat ini penting: keunikan yang memahami nilai null menentukan grup yang tepat, dan pemeriksaan tertunda mengizinkan benturan sementara di antara pembaruan.

Definisi mana yang sebaiknya digunakan pada versi PostgreSQL Anda?

PostgreSQL 15 memperkenalkan NULLS NOT DISTINCT untuk batasan dan indeks unik. Contoh utama di bawah memerlukan server versi 15 atau yang lebih baru; PostgreSQL 14 memerlukan alternatif yang ditampilkan nanti. Periksa versi server yang terhubung, bukan menyimpulkannya dari versi klien baris perintah yang terpasang. Catatan rilis PostgreSQL 15

Situasi Pendekatan yang didukung
PostgreSQL 15 atau yang lebih baru, tabel baru Batasan UNIQUE NULLS NOT DISTINCT bernama dengan DEFERRABLE INITIALLY IMMEDIATE
Batasan unik biasa yang sudah ada Ganti definisinya; ALTER CONSTRAINT tidak dapat mengubah sifat dapat ditunda pada batasan unik
Indeks unik berbasis ekspresi atau parsial yang sudah ada Ganti rancangan penegakannya; indeks tersebut tidak dapat dilampirkan sebagai batasan unik biasa
PostgreSQL 14 Gunakan kolom pengelompokan yang dihasilkan dengan batasan unik yang dapat ditunda

Batasan untuk objek yang sudah ada didokumentasikan dalam ALTER TABLE: perubahan atribut batasan dengan cara ini hanya berlaku untuk kunci asing, dan indeks yang dilampirkan sebagai batasan unik tidak boleh berisi ekspresi atau bersifat parsial. CREATE UNIQUE INDEX mandiri juga tidak memiliki klausa DEFERRABLE. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

Bagaimana cara menukar dua artikel akar dalam SQL?

Prasyaratnya adalah basis data PostgreSQL 15 atau yang lebih baru yang tersedia secara lokal dan koneksi psql yang berfungsi. Buka Terminal Mac dan mulai klien menggunakan pengaturan koneksi Anda. Perintah berikut melewati penyesuaian saat startup dan menghentikan pemrosesan skrip jika terjadi kesalahan. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Tempelkan demonstrasi SQL lengkap berikut ke sesi tersebut. Demonstrasi ini membuat tabel sementara yang hanya berisi artikel sintetis; tabel sementara akan hilang saat sesi berakhir. Nama tabel harus belum digunakan dalam sesi itu. PostgreSQL: Tabel Sementara

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;

Setelah pembaruan pertama, transaksi untuk sementara berisi dua akar pada posisi 1. Setelah pembaruan kedua, posisi keduanya kembali unik. Mengubah kembali batasan menjadi IMMEDIATE akan memeriksa perubahan yang masih tertunda pada saat itu. Jika duplikat masih ada, perintah tersebut gagal; jika tidak, transaksi dapat di-commit. Penundaan mengubah kapan validasi dilakukan. PostgreSQL: SET CONSTRAINTS

Urutan akhir akar yang diharapkan adalah:

Posisi ID Artikel Judul
1 2 Artikel B
2 1 Artikel A
3 3 Artikel C

Artikel anak mempertahankan posisinya karena tidak dipilih oleh kedua pembaruan. Jika transaksi interaktif mengalami kesalahan, jalankan ROLLBACK; sebelum mencoba lagi. Transaksi mengelompokkan pernyataan terkait hingga COMMIT atau ROLLBACK. PostgreSQL: BEGIN

Bagaimana cara mengurutkan ulang seluruh grup saudara dengan node-postgres?

Gunakan satu klien yang telah diambil dari pool untuk seluruh transaksi. node-postgres secara eksplisit mensyaratkan hal ini: pemanggilan pool.query() yang terpisah tidak cocok untuk transaksi karena dapat berjalan pada koneksi yang berbeda. node-postgres: Transaksi

Program berikut menerima daftar lengkap ID saudara yang terurut. Aturan tambahannya sederhana: daftar yang diberikan harus merupakan permutasi dari himpunan saudara saat ini. Menolak duplikat, ID yang tidak disertakan, dan ID dari grup lain mencegah pengurutan ulang parsial yang tampak berhasil.

Dalam proyek Node.js lokal, pasang pg, simpan program lengkap berikut sebagai reorder.cjs, lalu jalankan. Program ini mengasumsikan pengaturan koneksi lokal Anda tersedia melalui variabel lingkungan PostgreSQL. node-postgres mendukung variabel lingkungan koneksi yang sama dengan psql. node-postgres: Menghubungkan

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 membuat filter induk berfungsi untuk nilai null maupun non-null. Perbandingan biasa dengan parameter null tidak akan memilih akar. Untuk mengurutkan ulang artikel anak contoh, ubah parentId menjadi 1 dan orderedIds menjadi [5, 4]. PostgreSQL: Fungsi dan Operator Perbandingan

Placeholder bergaya $1 mengirim nilai secara terpisah dari teks SQL. Cast integer eksplisit juga menyatakan tipe yang dimaksud saat parameter induk bernilai null. node-postgres: Kueri

Mengapa perlu mendapatkan kunci sebelum membaca saudara?

Demonstrasi sementara tidak memiliki sesi lain yang bersaing. Kunci tabel menunjukkan pendekatan yang sengaja dibuat luas untuk tabel bersama berukuran kecil: SHARE ROW EXCLUSIVE berbenturan dengan penulisan tabel biasa dan dengan kunci yang sama, tetapi mengizinkan pembacaan biasa. Mempertahankan kunci selama transaksi menjaga keanggotaan saudara tetap stabil selama validasi dan pembaruan. Kunci ini juga memblokir penulisan pada grup induk yang tidak terkait, jadi pertimbangkan konsekuensi tersebut sebelum menggunakannya pada tabel yang sibuk. PostgreSQL: Penguncian Eksplisit

Program ini secara eksplisit memilih READ COMMITTED. Pada tingkat isolasi tersebut, kueri saudara melihat perubahan yang telah di-commit hingga awal kueri itu, termasuk perubahan yang di-commit sebelum penantian kunci sebelumnya selesai. PostgreSQL: BEGIN, PostgreSQL: Isolasi Transaksi

Validasi JavaScript diperiksa dengan permutasi yang valid serta ID duplikat, ID yang hilang, dan ID dari grup lain. Pemeriksaan deterministik tersebut berhasil. Contoh basis data ditinjau berdasarkan dokumentasi, tetapi tidak dijalankan pada PostgreSQL di lingkungan riset.

Apa alternatif yang didukung pada PostgreSQL 14?

Pada PostgreSQL 14, wujudkan ekspresi pengelompokan sebagai kolom yang dihasilkan, lalu terapkan batasan unik yang dapat ditunda pada kolom tersebut. Kolom hasil generate yang disimpan dihitung saat baris ditulis dan tidak dapat ditimpa dengan nilai biasa. PostgreSQL 14: Kolom Hasil Generate

Contoh SQL mandiri ini menggunakan dua nilai hasil generate untuk mewakili grup induk. Boolean membedakan akar dari ID induk sungguhan bernilai 0, sehingga nilai pengganti COALESCE tidak mengharuskan Anda mencadangkan suatu 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;

Untuk akar, nilai pengelompokan yang dihasilkan adalah (true, 0); untuk induk 0, nilainya adalah (false, 0). Setiap induk non-null mempertahankan nilai integernya masing-masing. Dengan demikian, batasan tersebut menyatakan pengelompokan yang dimaksud tanpa bergantung pada perlakuan PostgreSQL 15 terhadap nilai null. PostgreSQL 14 mendukung batasan unik yang dapat ditunda pada kolom bernama. PostgreSQL 14: CREATE TABLE

Apa yang perlu diperiksa sebelum menyesuaikan tabel yang sudah ada?

Indeks ekspresi pada COALESCE(parent_id, 0) dapat menegakkan keunikan berbasis ekspresi, tetapi tidak dapat begitu saja dibuat dapat ditunda atau dilampirkan sebagai batasan unik biasa. Indeks parsial untuk baris akar memiliki batasan pelampiran yang sama. Membiarkan salah satu objek penegakan langsung tersebut tetap ada dapat menghalangi pertukaran setelah batasan yang dapat ditunda ditambahkan. PostgreSQL: Indeks pada Ekspresi, PostgreSQL: ALTER TABLE

Sebelum mengganti definisi yang sudah ada, periksa pasangan induk-posisi yang duplikat, terutama akar yang sebelumnya diizinkan oleh keunikan nullable biasa. Menambahkan batasan unik akan memvalidasi baris yang sudah ada; NOT VALID tidak tersedia untuk batasan unik. Selesaikan posisi yang ambigu secara sengaja sebelum mencoba perubahan tersebut. PostgreSQL: ALTER TABLE

Pisahkan jaminan batasan dari aturan pengurutan aplikasi. Keunikan saja mengizinkan celah seperti posisi 1, 4, dan 9. Program daftar lengkap sengaja menetapkan posisi berurutan mulai dari 1. Pemeriksaan keanggotaannya memastikan setiap saudara ikut serta.

Untuk tugas ini, pilih batasan yang mewakili grup induk nullable, tunda pemeriksaannya sebelum pembaruan pertama yang menimbulkan benturan, validasi seluruh himpunan saudara yang diminta, lalu selesaikan semua pembaruan pada satu klien sebelum melakukan commit. Kombinasi ini mendukung pengurutan ulang akar dan anak sekaligus mempertahankan keunikan posisi akhir bagi setiap saudara.

Sumber