Upsert (ON CONFLICT) trong PostgreSQL

Tình huống mở đầu

Mỗi sáng nhân viên Tạp Hóa Cô Tấm quét mã vạch toàn bộ hàng trên kệ, và máy quét gửi lên một danh sách mã hàng cùng số lượng. Mặt hàng đã có trong bảng stock_levels thì phải cộng thêm số lượng, mặt hàng mới thì phải thêm dòng. Chị lập trình viên viết SELECT kiểm tra rồi mới INSERT hoặc UPDATE. Sáng thứ Hai có hai máy quét gửi cùng mã P4 gần như một lúc: cả hai đều thấy "chưa có", cả hai cùng INSERT, và một trong hai báo lỗi trùng khóa.

Hãy đoán trước: PostgreSQL có cách nào làm cả "thêm nếu chưa có, cập nhật nếu đã có" trong một lệnh duy nhất để hai máy quét không đụng nhau không?

Ôn nhanh bài trước: WITH moved AS (DELETE ... RETURNING *) INSERT ... khác hai lệnh DELETE và INSERT rời ở điểm nào?

Đáp án: nó là một lệnh, nên thành hoặc bại cùng nhau. Bài này cũng dựa vào ý đó: một lệnh upsert quyết định thêm hay sửa ngay bên trong, không cần bạn kiểm tra trước.

Mục tiêu bài học

Sau bài này bạn có thể:

• Viết INSERT ... ON CONFLICT DO NOTHING và DO UPDATE với EXCLUDED, và chọn đúng đích xung đột.
• Thêm điều kiện WHERE vào DO UPDATE, và biết RETURNING chỉ trả về các dòng đã thêm hoặc đã sửa.
• Giải thích lỗi 21000 khi một dòng bị chạm hai lần trong một lệnh, và cách gộp nguồn để tránh.
• Viết MERGE (PostgreSQL 15 trở lên) và so sánh với ON CONFLICT.

Bức tranh lớn

Xuyên suốt bài, bảng stock_levels là sổ tồn kho của Tạp Hóa Cô Tấm, và mỗi mã hàng là một trang riêng. Chỉ mục duy nhất trên sku là người giữ sổ: khi bạn đưa một dòng đề xuất, người đó tra xem trang của mã đó đã có chưa, rồi mới quyết định lập trang mới hay ghi thêm vào trang cũ. Dự án xuyên suốt là cập nhật tồn kho từ lượt quét mã vạch, database tên shop.

Hình dưới trả lời một câu hỏi: một dòng đề xuất có thể đi những nhánh nào, và nhánh nào làm thay đổi bảng?

Cách đọc hình: đọc từ trên xuống. Ô xanh dương là dòng đề xuất, ô cam là hai chỗ rẽ nhánh: có trùng khóa không, và điều kiện WHERE của DO UPDATE có đúng không. Hai ô xanh lá là hai cách bảng thay đổi (thêm dòng hoặc sửa dòng cũ). Ô tím là bỏ qua dòng, bảng không đổi. Chữ trong ô mới là nội dung chính, màu chỉ hỗ trợ.

Chỗ ẩn dụ không còn đúng: người giữ sổ thật tra và ghi lần lượt. Trong PostgreSQL, việc tra khóa và ghi là một bước không thể bị chen ngang, nên hai máy quét cùng lúc không còn "cùng thấy chưa có".

Khái niệm cốt lõi

Bài này có bốn khái niệm.

1. DO NOTHING

ON CONFLICT (sku) DO NOTHING bỏ qua dòng đề xuất nếu khóa đã có, dòng cũ giữ nguyên. Nếu không nêu đích xung đột (ON CONFLICT DO NOTHING) thì bất kỳ xung đột nào với ràng buộc duy nhất cũng bị bỏ qua. Chỉ xung đột duy nhất mới bị bỏ qua: lỗi NOT NULL hay CHECK vẫn là lỗi.

2. DO UPDATE và EXCLUDED

ON CONFLICT (sku) DO UPDATE SET ... sửa dòng cũ. Trong SET, tên bảng (stock_levels.qty) chỉ giá trị đang lưu, còn EXCLUDED.qty chỉ giá trị dòng đề xuất định ghi. Đích xung đột phải khớp một chỉ mục duy nhất, nếu không là lỗi; có thể gọi thẳng ràng buộc bằng ON CONFLICT ON CONSTRAINT tên. Mệnh đề WHERE đặt sau SET giới hạn dòng nào được sửa.

3. Một dòng chỉ bị chạm một lần

Trong một lệnh DO UPDATE, nếu hai dòng đề xuất cùng chạm một dòng đang có, PostgreSQL báo lỗi 21000 thay vì chọn một. Với DO NOTHING thì không có lỗi, dòng đến sau chỉ bị bỏ qua. RETURNING chỉ chứa các dòng đã thêm hoặc đã sửa.

4. MERGE

Từ PostgreSQL 15, MERGE so một bảng nguồn với bảng đích và cho từng trường hợp WHEN MATCHED hoặc WHEN NOT MATCHED hành động riêng: UPDATE, DELETE, INSERT. Nó linh hoạt hơn ON CONFLICT (có DELETE, nhiều điều kiện), nhưng dùng khóa nối chứ không dựa vào chỉ mục duy nhất.

Ví dụ đầu tiên

Ví dụ nhỏ nhất: bảng stock_levels có P1 tồn 10 và P2 tồn 5. Máy quét gửi lên P1 số lượng 99 và P3 số lượng 7. Mã P1 đã có, dùng DO NOTHING:

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P1', 99, 'scan'), ('P3', 7, 'scan')
ON CONFLICT (sku) DO NOTHING
RETURNING sku, qty;

Dự đoán trước khi xem kết quả: biên nhận RETURNING có mấy dòng, và P1 còn tồn bao nhiêu?

Kết quả [Đã chạy: PostgreSQL 16.15]:

 sku | qty 
-----+-----
 P3  |   7
(1 row)

INSERT 0 1

Thẻ lệnh INSERT 0 1: chỉ một dòng được thêm. Bảng sau lệnh:

SELECT sku, qty, updated_by FROM stock_levels ORDER BY sku;

 sku | qty | updated_by 
-----+-----+------------
 P1  |  10 | import
 P2  |   5 | import
 P3  |   7 | scan
(3 rows)

P1 vẫn là 10 và updated_by vẫn là import. Dòng đề xuất 99 không ghi lại gì cả.

Còn nếu muốn cộng dồn thì dùng DO UPDATE. Lần này P1 đề xuất cộng 3 và P4 là mã mới:

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P1', 3, 'scan'), ('P4', 7, 'scan')
ON CONFLICT (sku) DO UPDATE
SET qty = stock_levels.qty + EXCLUDED.qty,
    updated_by = EXCLUDED.updated_by,
    version = stock_levels.version + 1
RETURNING sku, qty, version;

 sku | qty | version 
-----+-----+---------
 P1  |  13 |       2
 P4  |   7 |       1
(2 rows)

INSERT 0 2

Bảng sau lệnh:

SELECT sku, qty, updated_by, version FROM stock_levels ORDER BY sku;

 sku | qty | updated_by | version 
-----+-----+------------+---------
 P1  |  13 | scan       |       2
 P2  |   5 | import     |       1
 P4  |   7 | scan       |       1
(3 rows)

Xem từng bước

Animation dưới đây chạy lại đúng lệnh DO UPDATE trên từng dòng đề xuất, rồi cho thấy điều gì xảy ra nếu có thêm một dòng đề xuất cùng khóa. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Bảng trace bằng chữ [Đã chạy: PostgreSQL 16.15, bảng có P1=10, P2=5]:

Bạn vừa làm được: đọc một lệnh upsert và nói trước dòng nào được thêm, dòng nào được sửa, dòng nào không bị chạm.

Concept Check

Dùng bảng stock_levels có P1=10 và P2=5.

Câu 1. Lệnh INSERT ... VALUES ('P1', 99, 'scan') ON CONFLICT (sku) DO NOTHING chạy xong. P1 có tồn kho bao nhiêu và thẻ lệnh là gì?

• A. 99, thẻ lệnh INSERT 0 1, vì dòng mới ghi đè dòng cũ.
• B. 109, thẻ lệnh INSERT 0 1, vì hai số được cộng với nhau.
• C. 10, thẻ lệnh INSERT 0 0, vì dòng đề xuất bị bỏ qua.
• D. Lỗi trùng khóa, vì DO NOTHING không xử lý khóa chính.

Câu 2. Trong DO UPDATE SET qty = stock_levels.qty + EXCLUDED.qty, EXCLUDED.qty là gì?

• A. Giá trị qty đang lưu trong dòng cũ.
• B. Giá trị qty mà dòng đề xuất định ghi.
• C. Tổng qty của cả bảng sau lệnh.
• D. Giá trị qty của dòng bị xóa trước đó.

Câu 3. Một lệnh DO UPDATE có hai dòng đề xuất cùng khóa P1 (cả hai trùng với dòng đang có). Kết quả?

• A. Cả hai dòng được áp dụng lần lượt, P1 nhận cả hai số.
• B. Chỉ dòng đầu được áp dụng, dòng sau bị bỏ qua.
• C. Lỗi 21000 và cả lệnh bị hủy.
• D. Chỉ dòng sau được áp dụng, dòng đầu bị bỏ qua.

Đáp án và giải thích:

• Câu 1: C. Bài đã chạy: P1 vẫn 10 và RETURNING không có P1. A và B là hành vi của DO UPDATE, D sai vì DO NOTHING chính là xử lý trùng khóa.
• Câu 2: B. EXCLUDED là dòng đề xuất bị loại ra khỏi lần chèn. A là stock_levels.qty, C và D bịa ra ý nghĩa khác.
• Câu 3: C. Bài đã chạy dup_err và nhận lỗi 21000. A, B, D đều giả định PostgreSQL tự chọn cách xử lý, trong khi nó chọn báo lỗi.

Làm cùng mình

Sáng nay máy quét báo hai dòng: P2 tăng 4 (đã có trong sổ) và P5 tồn 12 (mặt hàng mới). Bạn viết một lệnh cộng dồn P2 và thêm P5.

Bước 1. Vẽ ra giấy

Vẽ bảng stock_levels với P1=10, P2=5. Gạch dòng nào sẽ được thêm và dòng nào được sửa. Dự đoán: sau lệnh P2 và P5 có tồn bao nhiêu.

Bước 2. Viết lệnh

Điền hai chỗ trống:

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P2', 4, 'scan'), ('P5', 12, 'scan')
-- TODO 1: khi trùng khóa sku thì chuyển sang nhánh cập nhật
-- TODO 2: gán qty bằng qty đang lưu cộng qty đề xuất
RETURNING sku, qty;

Gợi ý cho chỗ trống thứ hai: tên bảng chỉ giá trị đang lưu, còn EXCLUDED chỉ giá trị đề xuất.

Bước 3. So kết quả

Đối chiếu với mục Lời giải và đáp án. Nếu gặp ON CONFLICT DO UPDATE requires inference specification, bạn đã quên nêu cột sku trong ngoặc sau ON CONFLICT.

Thử phá nó đi

Bốn bẫy hay gặp [Đã chạy: PostgreSQL 16.15].

Bẫy 1: hai dòng đề xuất cùng khóa trong một lệnh DO UPDATE.

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P1', 3, 'scan'), ('P1', 4, 'scan')
ON CONFLICT (sku) DO UPDATE SET qty = stock_levels.qty + EXCLUDED.qty;

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Cách sửa là gộp nguồn trước để mỗi khóa chỉ còn một dòng:

INSERT INTO stock_levels (sku, qty, updated_by)
SELECT sku, sum(qty), 'scan'
FROM (VALUES ('P1', 3), ('P1', 4)) AS v (sku, qty)
GROUP BY sku
ON CONFLICT (sku) DO UPDATE SET qty = stock_levels.qty + EXCLUDED.qty
RETURNING sku, qty;

 sku | qty 
-----+-----
 P1  |  17
(1 row)

INSERT 0 1

Với DO NOTHING, cùng tình huống đó không gây lỗi: dòng đến sau chỉ bị bỏ qua. Hai dòng cùng khóa P7 (chưa có) cho kết quả:

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P7', 3, 'scan'), ('P7', 4, 'scan')
ON CONFLICT (sku) DO NOTHING
RETURNING sku, qty;

 sku | qty 
-----+-----
 P7  |   3
(1 row)

INSERT 0 1

Bẫy 2: nêu sai đích xung đột. Cột qty không có chỉ mục duy nhất, còn DO UPDATE thiếu đích. Hai lệnh dưới đều lỗi, nên thông báo lỗi hiện liền nhau:

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P2', 1, 'scan')
ON CONFLICT (qty) DO NOTHING;
INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P2', 1, 'scan')
ON CONFLICT DO UPDATE SET qty = 1;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 3: ON CONFLICT DO UPDATE SET qty = 1;
        ^
HINT:  For example, ON CONFLICT (column_name).

Bẫy 3: nghĩ DO NOTHING bỏ qua mọi lỗi. Dòng đề xuất có qty là NULL vẫn bị từ chối vì NOT NULL:

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P2', NULL, 'scan')
ON CONFLICT (sku) DO NOTHING;

ERROR:  null value in column "qty" of relation "stock_levels" violates not-null constraint
DETAIL:  Failing row contains (P2, null, scan, 1).

Bẫy 4: bộ đếm identity vẫn đi tới khi dòng bị bỏ qua. Bảng products có id identity, lệnh dưới nhập P1 hai lần bằng DO NOTHING rồi nhập P2:

INSERT INTO products (sku, name, price) VALUES ('P1', 'Pen', 12.50);
INSERT INTO products (sku, name, price) VALUES ('P1', 'Pen', 12.50) ON CONFLICT (sku) DO NOTHING;
INSERT INTO products (sku, name, price) VALUES ('P1', 'Pen', 12.50) ON CONFLICT (sku) DO NOTHING;
INSERT INTO products (sku, name, price) VALUES ('P2', 'Pencil', 3.00) RETURNING id;

INSERT 0 1
INSERT 0 0
INSERT 0 0
 id 
----
  4
(1 row)

INSERT 0 1

P2 nhận id 4 chứ không phải 2. Hai lần DO NOTHING đã tiêu hai số.

Hình dưới đối chiếu hiểu lầm lớn nhất về biên nhận RETURNING của upsert.

Cách đọc hình: hai chuỗi độc lập. Chuỗi đỏ là niềm tin sai: biên nhận liệt kê mọi dòng đề xuất. Chuỗi xanh là thực tế đã chạy ở ví dụ đầu: P1 bị bỏ qua nên không có trong biên nhận. Muốn biết có dòng nào bị bỏ qua, so thẻ lệnh với số dòng đã gửi.

Mệnh đề WHERE của DO UPDATE còn dùng để không ghi đè dữ liệu mới bằng dữ liệu cũ. Ví dụ P1 đã có version 2 sau lệnh do_update, còn P2 có version 1:

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P1', 50, 'old-scan'), ('P2', 50, 'old-scan')
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty, updated_by = EXCLUDED.updated_by
WHERE stock_levels.version < 2
RETURNING sku, qty;

 sku | qty 
-----+-----
 P2  |  50
(1 row)

INSERT 0 1

Chỉ P2 được sửa, vì P1 có version 2 không thỏa điều kiện version < 2. Biên nhận cũng chỉ có P2.

Bây giờ so với MERGE. Bảng nguồn scan_feed có P1 cộng 3, P2 số lượng 0 (nghĩa là hết hàng, xóa khỏi sổ) và P5 mới:

MERGE INTO stock_levels AS t
USING scan_feed AS f ON t.sku = f.sku
WHEN MATCHED AND f.qty = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET qty = t.qty + f.qty, version = t.version + 1
WHEN NOT MATCHED THEN
  INSERT (sku, qty, updated_by) VALUES (f.sku, f.qty, 'merge');
SELECT sku, qty, version FROM stock_levels ORDER BY sku;

MERGE 3
 sku | qty | version 
-----+-----+---------
 P1  |  13 |       2
 P5  |  12 |       1
(2 rows)

Thẻ lệnh MERGE 3 cho biết ba dòng nguồn đã được xử lý. Hai điểm khác ON CONFLICT mà bạn cần nhớ: MERGE cũng báo 21000 khi một dòng đích khớp hai dòng nguồn, và ở PostgreSQL 16 nó chưa hỗ trợ RETURNING.

MERGE INTO stock_levels AS t
USING scan_feed AS f ON t.sku = f.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + f.qty
WHEN NOT MATCHED THEN INSERT (sku, qty, updated_by) VALUES (f.sku, f.qty, 'merge');

ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

MERGE INTO stock_levels AS t
USING scan_feed AS f ON t.sku = f.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + f.qty
RETURNING t.sku;

ERROR:  syntax error at or near "RETURNING"
LINE 4: RETURNING t.sku;
        ^

Tự tay làm

Dùng chung bảng stock_levels (P1=10, P2=5) của bài. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.

Cơ bản

Bài 1. Đoán biên nhận. Đầu vào: lệnh INSERT ... VALUES ('P1', 1, 'x'), ('P2', 1, 'x'), ('P3', 1, 'x') ON CONFLICT (sku) DO NOTHING RETURNING sku. Điền bảng:

Tiêu chí đạt: điền đúng cả ba ô. Gợi ý: DO NOTHING bỏ qua dòng trùng khóa.

Vận dụng

Bài 2. Chỉ ghi khi số lượng đổi. Đầu vào: máy quét gửi P1 số lượng 10 (giống số đang lưu) và P2 số lượng 6. Viết lệnh đặt qty bằng giá trị đề xuất, nhưng chỉ sửa dòng nào có số lượng thực sự khác, để tránh ghi vô ích. Tiêu chí đạt: biên nhận chỉ có P2 với số lượng 6. Gợi ý: dùng IS DISTINCT FROM trong WHERE của DO UPDATE.

Thử thách (tùy chọn)

Bài 3. Phiếu quét trùng mã. Đầu vào: bảng scan_feed có P1 cộng 3, P1 cộng 4, P5 cộng 12. Viết lệnh upsert cộng tất cả vào stock_levels mà không gặp lỗi 21000. Tiêu chí đạt: P1 bằng 17 và P5 bằng 12. Gợi ý: gộp scan_feed bằng GROUP BY sku trước khi upsert.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.

1. (Mục tiêu 1, nhớ lại) EXCLUDED trong DO UPDATE là gì?
2. (Mục tiêu 1, dự đoán) ON CONFLICT (sku) DO NOTHING bỏ qua dòng vi phạm NOT NULL hay chỉ dòng trùng sku?
3. (Mục tiêu 2, giải thích) Vì sao RETURNING của một lệnh upsert có thể ít dòng hơn số dòng đề xuất?
4. (Mục tiêu 3, giải thích cho bạn chưa học) Vì sao hai dòng đề xuất cùng khóa gây lỗi trong DO UPDATE nhưng không gây lỗi trong DO NOTHING?
5. (Mục tiêu 4, so sánh) Nêu một việc MERGE làm được mà ON CONFLICT không làm được.

Điều cần nhớ

• ON CONFLICT DO NOTHING bỏ qua dòng trùng khóa; DO UPDATE sửa dòng cũ với EXCLUDED là giá trị đề xuất.
• Đích xung đột phải khớp một chỉ mục duy nhất, hoặc gọi ràng buộc bằng ON CONSTRAINT.
• Mệnh đề WHERE của DO UPDATE lọc dòng được sửa; RETURNING chỉ trả dòng đã thêm hoặc đã sửa.
• Hai dòng đề xuất cùng khóa trong DO UPDATE gây lỗi 21000; phải gộp nguồn trước.
• MERGE (từ PostgreSQL 15) có thêm DELETE và nhiều điều kiện, nhưng ở 16 chưa có RETURNING.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: chương sau nói về ràng buộc như PRIMARY KEY, UNIQUE, CHECK. Ràng buộc nào bạn vừa thấy làm ON CONFLICT hoạt động, và có thể tự tạo ra nó thế nào?

Đào sâu

Phần này dành cho bạn tò mò.

• Cơ chế bên trong. Cột hệ thống xmax bằng 0 trên dòng vừa thêm, khác 0 trên dòng vừa sửa, nên đôi khi người ta dùng (xmax = 0) để biết dòng nào là thêm mới. Đây là mẹo dựa vào chi tiết nội bộ, không được tài liệu cam kết, đừng dùng cho logic quan trọng [Đã chạy: PostgreSQL 16.15 nhưng có thể khác ở bản khác]:

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P1', 1, 'scan'), ('P6', 2, 'scan')
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty
RETURNING sku, (xmax = 0) AS inserted;

 sku | inserted 
-----+----------
 P1  | f
 P6  | t
(2 rows)

INSERT 0 2

• So sánh với lựa chọn khác. Theo tài liệu, INSERT ... ON CONFLICT DO UPDATE bảo đảm kết quả thêm hoặc sửa ngay cả khi có nhiều phiên cùng chạy. MERGE thì có thể gặp lỗi trùng khóa nếu phiên khác thêm cùng khóa xen vào giữa [Suy luận: theo mục Notes của trang MERGE; bài chưa mở hai phiên để chứng minh].
• Hiểu lầm nâng cao. Dòng bị WHERE loại khỏi DO UPDATE không được sửa, nhưng nó vẫn đã làm bộ đếm identity đi tới như mọi dòng đề xuất khác. Cách thực dụng để tránh ghi vô ích là WHERE ... IS DISTINCT FROM, như Bài 2.
• Giới hạn của bài. Bài này chưa nói đến chỉ mục một phần (partial index) làm đích xung đột, ràng buộc loại trừ, MERGE với nhiều nguồn, hay RETURNING của MERGE (có ở phiên bản mới hơn 16) [Suy luận: máy này chỉ có 16.15].
• Đọc thêm. PostgreSQL 16 Documentation, trang INSERT mục ON CONFLICT: https://www.postgresql.org/docs/16/sql-insert.html và trang MERGE: https://www.postgresql.org/docs/16/sql-merge.html

Bài tiếp theo

Chương này kết thúc ở đây. Bài kiểm tra chương gồm 45 câu bao phủ cả 5 bài. Chương kế tiếp nói về ràng buộc và khóa trong PostgreSQL, nơi bạn học cách tự tạo các chỉ mục duy nhất mà ON CONFLICT đang dựa vào. Mini-dự án nối tiếp: thiết kế ràng buộc cho bảng tồn kho của Tạp Hóa Cô Tấm.

Lời giải và đáp án

Làm cùng mình. Hai chỗ trống điền:

ON CONFLICT (sku) DO UPDATE
SET qty = stock_levels.qty + EXCLUDED.qty

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P2', 4, 'scan'), ('P5', 12, 'scan')
ON CONFLICT (sku) DO UPDATE
SET qty = stock_levels.qty + EXCLUDED.qty
RETURNING sku, qty;

 sku | qty 
-----+-----
 P2  |   9
 P5  |  12
(2 rows)

INSERT 0 2

P2 từ 5 thành 9, còn P5 được thêm với 12. Thẻ lệnh INSERT 0 2 vì cả hai dòng đều bị ảnh hưởng.

Bài 1.

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P1', 1, 'x'), ('P2', 1, 'x'), ('P3', 1, 'x')
ON CONFLICT (sku) DO NOTHING
RETURNING sku;
SELECT sku, qty FROM stock_levels ORDER BY sku;

 sku 
-----
 P3
(1 row)

INSERT 0 1
 sku | qty 
-----+-----
 P1  |  10
 P2  |   5
 P3  |   1
(3 rows)

Biên nhận chỉ có P3, thẻ lệnh INSERT 0 1, P1 vẫn 10. Hai dòng trùng khóa bị bỏ qua.

Bài 2.

INSERT INTO stock_levels (sku, qty, updated_by)
VALUES ('P1', 10, 'scan'), ('P2', 6, 'scan')
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty, updated_by = EXCLUDED.updated_by
WHERE stock_levels.qty IS DISTINCT FROM EXCLUDED.qty
RETURNING sku, qty;

 sku | qty 
-----+-----
 P2  |   6
(1 row)

INSERT 0 1

Bài 3.

INSERT INTO stock_levels (sku, qty, updated_by)
SELECT sku, sum(qty), 'scan' FROM scan_feed GROUP BY sku
ON CONFLICT (sku) DO UPDATE
SET qty = stock_levels.qty + EXCLUDED.qty
RETURNING sku, qty;

 sku | qty 
-----+-----
 P1  |  17
 P5  |  12
(2 rows)

INSERT 0 2

Tự kiểm tra.

1. Dòng đề xuất định ghi bị loại ra do xung đột; trong SET bạn đọc các cột của nó bằng EXCLUDED.cột.
2. Chỉ dòng trùng khóa duy nhất. Dòng vi phạm NOT NULL hay CHECK vẫn gây lỗi.
3. Vì dòng bị bỏ qua (nhánh DO NOTHING hoặc WHERE sai) không thêm cũng không sửa, nên không nằm trong RETURNING.
4. DO UPDATE sẽ phải sửa một dòng hai lần trong một lệnh, nên PostgreSQL từ chối. DO NOTHING chỉ bỏ dòng đến sau, không sửa gì nên không có xung đột.
5. MERGE có thể xóa dòng (WHEN MATCHED THEN DELETE) và đặt nhiều điều kiện khác nhau cho nhiều nhánh, ON CONFLICT chỉ thêm hoặc cập nhật.