Upsert ด้วย ON CONFLICT

บางครั้งคุณอยากจะ insert row หนึ่ง แต่ถ้ามีตัวที่ตรงกันอยู่แล้ว คุณอยาก update row เดิมมากกว่าปล่อยให้ล้มเหลว การทำสิ่งนี้ด้วยมือ — ตรวจสอบก่อน แล้วค่อย insert หรือ update — มี race condition: คำขอสองอันสามารถเห็น “ไม่มี row” ทั้งคู่ และพยายาม insert ทั้งคู่ได้ PostgreSQL แก้ปัญหานี้แบบ atomic ด้วย INSERT ... ON CONFLICT ซึ่งมักเรียกว่า upsert (update บวก insert)

สำหรับตัวอย่างเหล่านี้ ลองจินตนาการว่า table books มี unique constraint บน title ดังนั้นจึงไม่มีหนังสือสองเล่มที่มี title เหมือนกันเป๊ะ ๆ ได้

ALTER TABLE books ADD CONSTRAINT books_title_key UNIQUE (title);

upsert ตัดสินใจอย่างไร

เมื่อ insert จะละเมิด unique constraint ที่ระบุชื่อไว้ PostgreSQL จะไม่ขึ้น error แต่จะรัน action ใน ON CONFLICT clause ของคุณแทน — ไม่ว่าจะเป็นการ update row ที่มีอยู่ หรือไม่ทำอะไรเลยอย่างเงียบ ๆ

flowchart TD
  A[INSERT row] --> B{Conflicts with unique constraint?}
  B -- No --> C[Insert the new row]
  B -- Yes --> D{ON CONFLICT action}
  D -- DO UPDATE --> E[Update the existing row]
  D -- DO NOTHING --> F[Leave the existing row unchanged]

ON CONFLICT ทำอะไรเมื่อเจอข้อมูลซ้ำ

DO UPDATE ด้วย EXCLUDED

คุณระบุชื่อ column (หรือ constraint) ที่นิยาม conflict แล้วอธิบายว่าจะ update อย่างไร ภายในการ update นั้น table พิเศษ EXCLUDED เก็บค่าที่คุณ พยายาม จะ insert ไว้ คุณจึงคัดลอกค่าเหล่านั้นลงใน row เดิมได้

psql

INSERT INTO books (title, author_id, published, copies_sold)
VALUES ('The Glass Orchard', 1, 2005, 1200)
ON CONFLICT (title)
DO UPDATE SET
  published   = EXCLUDED.published,
  copies_sold = EXCLUDED.copies_sold
RETURNING id, title, copies_sold;

Node.js

const res = await pool.query(
  `INSERT INTO books (title, author_id, published, copies_sold)
   VALUES ($1, $2, $3, $4)
   ON CONFLICT (title)
   DO UPDATE SET published = EXCLUDED.published,
                 copies_sold = EXCLUDED.copies_sold
   RETURNING id, title, copies_sold`,
  ['The Glass Orchard', 1, 2005, 1200],
);

Python

cur.execute(
    """
    INSERT INTO books (title, author_id, published, copies_sold)
    VALUES (%s, %s, %s, %s)
    ON CONFLICT (title)
    DO UPDATE SET published = EXCLUDED.published,
                  copies_sold = EXCLUDED.copies_sold
    RETURNING id, title, copies_sold
    """,
    ("The Glass Orchard", 1, 2005, 1200),
)

Go

rows, err := conn.Query(ctx,
    `INSERT INTO books (title, author_id, published, copies_sold)
     VALUES ($1, $2, $3, $4)
     ON CONFLICT (title)
     DO UPDATE SET published = EXCLUDED.published,
                   copies_sold = EXCLUDED.copies_sold
     RETURNING id, title, copies_sold`,
    "The Glass Orchard", 1, 2005, 1200)

Rust

let rows = sqlx::query(
    "INSERT INTO books (title, author_id, published, copies_sold)
     VALUES ($1, $2, $3, $4)
     ON CONFLICT (title)
     DO UPDATE SET published = EXCLUDED.published,
                   copies_sold = EXCLUDED.copies_sold
     RETURNING id, title, copies_sold",
)
.bind("The Glass Orchard")
.bind(1_i64)
.bind(2005_i32)
.bind(1200_i32)
.fetch_all(&pool)
.await?;

เนื่องจากหนังสือชื่อ “The Glass Orchard” มีอยู่แล้ว การ insert จึงกลายเป็นการ update ของ row นั้น:

 id |       title       | copies_sold
----+-------------------+-------------
  1 | The Glass Orchard |        1200
(1 row)

รันคำสั่งเดียวกันด้วย title ใหม่เอี่ยม แล้วจะไม่มี conflict จึง insert ตามปกติและคืน id ที่ถูกสร้างขึ้นใหม่สด ๆ

ใน pgAdmin: รันคำสั่งสองครั้งใน Query Tool — การรันครั้งแรก insert ครั้งที่สองชน conflict และ update table Data Output แสดง row ที่ถูกคืนกลับมาในแต่ละครั้ง

DO NOTHING

ถ้าคุณต้องการ insert เฉพาะตอนที่ row เป็นของใหม่ และข้ามตัวซ้ำไปเงียบ ๆ ให้ใช้ DO NOTHING ไม่มี error, ไม่มี update

psql

INSERT INTO books (title, author_id, published)
VALUES ('The Glass Orchard', 1, 2005)
ON CONFLICT (title) DO NOTHING;

Node.js

const res = await pool.query(
  'INSERT INTO books (title, author_id, published) VALUES ($1, $2, $3) ON CONFLICT (title) DO NOTHING',
  ['The Glass Orchard', 1, 2005],
);
console.log(res.rowCount); // 0 when the row already existed

Python

cur.execute(
    "INSERT INTO books (title, author_id, published) VALUES (%s, %s, %s) ON CONFLICT (title) DO NOTHING",
    ("The Glass Orchard", 1, 2005),
)
# cur.rowcount is 0 when nothing was inserted

Go

tag, err := conn.Exec(ctx,
    "INSERT INTO books (title, author_id, published) VALUES ($1, $2, $3) ON CONFLICT (title) DO NOTHING",
    "The Glass Orchard", 1, 2005)
// tag.RowsAffected() is 0 when the row already existed

Rust

let result = sqlx::query(
    "INSERT INTO books (title, author_id, published) VALUES ($1, $2, $3) ON CONFLICT (title) DO NOTHING",
)
.bind("The Glass Orchard")
.bind(1_i64)
.bind(2005_i32)
.execute(&pool)
.await?;
// result.rows_affected() is 0 when nothing was inserted

เมื่อ row มีอยู่แล้ว จะไม่มี row ใดได้รับผลกระทบ แต่ถ้าเป็น row ใหม่ ก็จะ insert เพิ่มหนึ่ง row

เคล็ดลับและข้อควรระวัง

• upsert ต้องการ unique constraint หรือ unique index เพื่อตรวจจับ conflict ON CONFLICT (title) ทำงานได้เพราะ title เป็น unique เท่านั้น หากไม่มีสิ่งนั้น PostgreSQL ก็ไม่มีแนวคิดเรื่องข้อมูลซ้ำ
• table EXCLUDED อ้างถึง row ที่คุณพยายาม insert ใช้ใน DO UPDATE เพื่อดึงค่าใหม่เข้าไปใน row ที่มีอยู่
• คุณสามารถเพิ่ม WHERE ให้กับ DO UPDATE เพื่อ update เฉพาะภายใต้เงื่อนไขบางอย่าง เช่น DO UPDATE SET copies_sold = EXCLUDED.copies_sold WHERE EXCLUDED.copies_sold > books.copies_sold
• DO NOTHING คืนค่า row ที่ได้รับผลกระทบเป็นศูนย์เมื่อเกิด conflict ดังนั้นจงตรวจสอบจำนวน row หากคุณจำเป็นต้องรู้ว่า insert เกิดขึ้นจริงหรือไม่
• จงส่งค่าผ่าน placeholder ตรงนี้ด้วยเช่นกัน upsert ยังคงเป็น INSERT และกฎ SQL injection เดียวกันก็ยังใช้ได้

🐘 คำสั่ง atomic เดียว

ชัยชนะที่แท้จริงของ ON CONFLICT คือความเป็น atomic การตรวจสอบแล้วค่อย insert ในโค้ดแอปพลิเคชันอาจแพ้ race ระหว่างคำขอสองอันได้ แต่คำสั่ง upsert เดียวจะ insert หรือ update เพียงครั้งเดียวพอดี โดยมี unique constraint บังคับความถูกต้องให้คุณ

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
INSERT ... ON CONFLICT	atomic, round trip เดียว, ไม่มี race condition	ต้องมี unique constraint หรือ unique index ให้ conflict อ้างอิงได้
ตรวจสอบก่อนแล้วค่อย insert ในโค้ดแอป (check-then-insert)	อ่านง่าย เข้าใจ logic ได้ตรงไปตรงมา	เป็น race condition แบบคลาสสิกเมื่อมีหลาย request พร้อมกัน
DO UPDATE เมื่อชน conflict	ข้อมูลถูก sync ให้เป็นค่าล่าสุดเสมอ	overwrite ค่าที่มีอยู่แล้วโดยไม่ตั้งใจได้ถ้าไม่ระวัง
DO NOTHING เมื่อชน conflict	ปลอดภัย ไม่เผลอเขียนทับข้อมูลเดิม	ข้อมูลซ้ำจะถูกละเว้นอย่างเงียบ ๆ แม้บางครั้งควรจะ update จริง

ข้อผิดพลาดที่พบบ่อย

• ใช้ SELECT ตรวจสอบว่า row มีอยู่ก่อน แล้วค่อย INSERT ในโค้ดแอปพลิเคชัน — race กันได้เมื่อมีหลาย request พร้อมกันเห็น “ไม่มี row” ทั้งคู่ ให้ใช้ ON CONFLICT แทนเพื่อความ atomic
• ลืมว่า ON CONFLICT ต้องมี unique/exclusion constraint ให้เล็ง — ถ้า column ที่ระบุใน ON CONFLICT (...) ไม่ใช่ unique PostgreSQL จะขึ้น error ทันที
• ใช้ DO NOTHING ทั้งที่ต้องการให้ row ถูก update — ทำให้ข้อมูลเก่าค้างอยู่โดยไม่รู้ตัว ต้องเลือกระหว่าง DO UPDATE กับ DO NOTHING ให้ตรงกับ intent จริง

💡 ตัวอย่างจากของจริง

ระบบ checkout ของ e-commerce — ใช้ ON CONFLICT เพื่อสร้าง order แบบ idempotent เมื่อ client retry request ที่ล้มเหลว (เช่น network timeout) โดยไม่สร้าง order ซ้ำซ้อน

ระบบจัดการ inventory / stock — ใช้ upsert primitive แบบเดียวกันเพื่อรับ event อัปเดตจำนวนสินค้าที่อาจถูกส่งซ้ำจาก message queue โดยไม่ทำให้ตัวเลข stock ผิดเพี้ยนจาก race condition

column ต้องมีอะไรอยู่ก่อน ON CONFLICT (col) ถึงจะตรวจจับค่าซ้ำได้?

foreign key unique constraint หรือ unique index NOT NULL constraint default value

ใน DO UPDATE ค่า EXCLUDED.copies_sold หมายถึงอะไร?

ค่าที่เก็บอยู่ใน row เดิมตอนนี้ ค่าที่เราพยายาม insert เข้าไป เป็น 0 เสมอ row สุ่มจาก table

ON CONFLICT (title) DO NOTHING ทำอะไรเมื่อ title นั้นมีอยู่แล้ว?

เกิด unique violation error อัปเดต row เดิม ข้ามการ insert โดยไม่กระทบ row ใด ๆ ลบ row เดิมทิ้ง

Submit

Mark as complete