Menu

Upsert trong PostgreSQL với INSERT ... ON CONFLICT

Upsert trong PostgreSQL với INSERT ... ON CONFLICT: DO UPDATE với EXCLUDED, DO NOTHING, cập nhật có điều kiện, bộ đếm, và phân biệt hàng được chèn với hàng được cập nhật.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

PostgreSQL không có từ khóa UPSERT. Bạn viết INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Khi hàng mới sẽ vi phạm một ràng buộc unique trên cột đó, PostgreSQL cập nhật hàng hiện có thay vì chèn. DO NOTHING thì bỏ qua nó.

A-100 đã tồn tại, nên giá của nó đổi thành 12,5. B-200 là hàng mới và được chèn vào. Một câu lệnh xử lý cả hai, và vẫn đúng khi các phiên khác cùng lúc chèn những khóa đó.

Chèn hoặc cập nhật một hàng

Câu lệnh có ba phần:

INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
  • Conflict target ghi các cột của một index hoặc ràng buộc unique, như khóa chính. Nó phải khớp chính xác với một cái, nếu không PostgreSQL báo lỗi "there is no unique or exclusion constraint matching the ON CONFLICT specification". Bạn có thể ghi tên ràng buộc thay vào đó: ON CONFLICT ON CONSTRAINT products_pkey.
  • EXCLUDED là hàng bạn đã cố chèn. EXCLUDED.price là giá mới, còn products.price là giá đang được lưu.
  • DO UPDATE SET chỉ thay đổi những cột bạn liệt kê. Các cột khác của hàng hiện có giữ nguyên giá trị.

Một index unique có mệnh đề WHERE (partial index) cũng dùng được làm target, nếu bạn lặp lại điều kiện của nó: ON CONFLICT (email) WHERE deleted_at IS NULL.

Bỏ qua trùng lặp với DO NOTHING

DO NOTHING chèn những hàng mới và bỏ những hàng bị xung đột, không báo lỗi. Khi không có conflict target, nó bỏ qua hàng nào vi phạm bất kỳ ràng buộc unique nào.

RETURNING chỉ liệt kê lin@example.com. Hàng bị bỏ qua không được trả về, nên DO NOTHING ... RETURNING cho biết hàng nào là mới.

Cộng dồn vào bộ đếm

Để tăng một giá trị đã lưu thay vì thay thế nó, hãy cộng EXCLUDED vào giá trị hiện tại. Ghi rõ tên bảng trước giá trị hiện tại. Bên trong DO UPDATE, một views trơn khớp với cả bảng lẫn EXCLUDED, và PostgreSQL dừng lại với lỗi "column reference "views" is ambiguous".

Câu lệnh đầu chèn hàng với giá trị 1 và hai câu tiếp theo cộng thêm vào, nên /home kết thúc ở 3.

Chỉ cập nhật khi giá trị thay đổi

Một WHERE sau DO UPDATE SET quyết định, cho từng hàng, có chạy cập nhật hay không. Khi nó sai, hàng được giữ nguyên và không được tính là chèn hay cập nhật. Hãy dùng nó để chỉ giữ dữ liệu mới nhất, hoặc để tránh ghi một phiên bản hàng mới khi không có gì thay đổi.

A-100 giữ 10 vì hàng đi vào cũ hơn. B-200 nhận 25. Để bỏ qua những lần cập nhật sẽ ghi lại cùng giá trị, hãy dùng WHERE prices.price IS DISTINCT FROM EXCLUDED.price, cách này cũng xử lý được NULL.

Phân biệt hàng được chèn với hàng được cập nhật

RETURNING dùng được với upsert như với mọi lệnh chèn. Để phân biệt hai trường hợp, hãy đọc cột hệ thống xmax, cột này bằng 0 với hàng mà câu lệnh này đã chèn:

A-100 trả về 8 với inserted là false, C-300 trả về 7 với true. xmax là chi tiết nội bộ, không phải API có tài liệu, nên hãy coi nó là một tiện ích. Trang RETURNING trình bày đầy đủ mệnh đề này.

Những lỗi bạn sẽ gặp

  • "ON CONFLICT DO UPDATE command cannot affect row a second time": cùng một khóa xuất hiện hai lần trong VALUES hoặc SELECT của một câu lệnh. Hãy bỏ trùng lặp trước, ví dụ bằng SELECT DISTINCT ON (sku) ....
  • "column reference is ambiguous": ghi rõ tên bảng trước cột trong các biểu thức SET và WHERE, như ở trên.
  • Id identity hoặc serial bị nhảy cóc: một lệnh chèn chuyển thành cập nhật đã lấy một giá trị từ sequence, nên id bị bỏ qua vài số. Điều này là bình thường.

MERGE (PostgreSQL 15 trở lên) cũng chèn hoặc cập nhật được, nhưng không chống được việc hai phiên cùng lúc chèn một khóa. Với upsert theo khóa, hãy dùng ON CONFLICT. Tài liệu tham khảo INSERT chính thức liệt kê mọi tùy chọn.

Câu hỏi thường gặp

PostgreSQL có câu lệnh UPSERT không?

Không có từ khóa UPSERT. PostgreSQL 9.5 thêm INSERT ... ON CONFLICT, lệnh này chèn một hàng hoặc, khi việc chèn sẽ vi phạm một ràng buộc unique, cập nhật hàng hiện có (DO UPDATE) hay bỏ qua nó (DO NOTHING).

INSERT ... ON CONFLICT có an toàn khi chèn đồng thời không?

Có. Mỗi hàng đề xuất đều kết thúc bằng một lần chèn hoặc một lần cập nhật, kể cả khi các phiên khác chèn cùng khóa vào cùng thời điểm. Một lệnh SELECT rồi INSERT hoặc UPDATE trong code ứng dụng không cho đảm bảo đó.

Nên dùng MERGE hay ON CONFLICT để upsert?

Hãy dùng ON CONFLICT để chèn hoặc cập nhật theo khóa. MERGE (PostgreSQL 15 trở lên) xử lý được nhiều trường hợp hơn, như xóa những hàng khớp. Khi có nhiều lệnh chèn đồng thời cùng một khóa, nó có thể thất bại vì vi phạm unique, điều mà ON CONFLICT tránh được.

Vì sao lại gặp lỗi "there is no unique or exclusion constraint matching the ON CONFLICT specification"?

Các cột trong ON CONFLICT (...) phải khớp chính xác với một index hoặc ràng buộc unique. Hãy thêm ràng buộc UNIQUE hoặc index unique trên các cột đó, hoặc ghi đúng các cột của một ràng buộc đã có.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu