Bagaimana untuk Mendapatkan ID Baris yang Disisipkan dan Bercanggah Menggunakan PostgreSQL's ON CONFLIT?

Mendapatkan semula ID daripada PostgreSQL UPSERTs dengan ON CONFLIT

Cabaran: Standard PostgreSQL 9.5 ON CONFLICT DO NOTHING dengan RETURNING id tidak mengembalikan ID untuk baris yang bercanggah. Matlamatnya adalah untuk mendapatkan semula ID kedua-dua baris yang baru dimasukkan dan baris sedia ada yang terlibat dalam konflik semasa operasi upsert.

Penyelesaian:

Pendekatan dipertingkat ini menggunakan klausa WITH untuk mengendalikan perkara ini dengan cekap:

<code class="language-sql">WITH existing_rows AS (
    SELECT id
    FROM chats
    WHERE ("user", "contact") = ANY (SELECT ("user", "contact") FROM temp_table)
), inserted_rows AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT "user", "contact", "name"
    FROM temp_table
    ON CONFLICT ("user", "contact") DO NOTHING
    RETURNING id
)
SELECT id FROM existing_rows
UNION ALL
SELECT id FROM inserted_rows;</code>

temp_table mengandungi data yang hendak disisipkan.

Penjelasan:

1. existing_rows CTE: Ini memilih ids baris yang sedia ada dalam jadual chats yang sepadan dengan pasangan ("user", "contact") dalam temp_table.

2. inserted_rows CTE: Ini melaksanakan pernyataan INSERT dengan ON CONFLICT DO NOTHING dan RETURNING id, menangkap ids baris yang baru dimasukkan.

3. UNION ALL: Ini menggabungkan hasil daripada kedua-dua CTE, memberikan senarai lengkap idyang terjejas oleh operasi upsert.

Pertimbangan Penting:

• Konkurensi: Dalam persekitaran konkurensi tinggi, data mungkin berubah antara operasi SELECT dan INSERT. Pertimbangkan untuk menggunakan urus niaga atau mekanisme kawalan serentak yang lebih mantap untuk integriti data.
• Jenis Data: Pastikan jenis data dalam temp_table sepadan dengan jenis data dalam jadual chats. Penghantaran eksplisit mungkin diperlukan.
• Keunikan: Kekalkan keunikan dalam data temp_table (atau gunakan pengecam unik) untuk mengelakkan berbilang kemas kini yang tidak diingini pada baris yang sama.

Penyelesaian yang diperhalusi ini menawarkan kaedah yang lebih jelas dan cekap untuk mendapatkan semula semua ID yang berkaitan semasa operasi upsert PostgreSQL dengan pengendalian konflik.

Atas ialah kandungan terperinci Bagaimana untuk Mendapatkan ID Baris yang Disisipkan dan Bercanggah Menggunakan PostgreSQL's ON CONFLIT?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!