pgsql-hackers
❮
COPY ON_CONFLICT TABLE; save duplicated record to another table.
- Jump to comment-1jian he<jian.universality@gmail.com>Apr 25, 2026, 4:13 AM UTCHi,
This is for v20.
Reference: https://web.archive.org/web/20240328094030/https://riggs.business/blog/f/postgresql-todo-2023
COPY enhancement:
Detect duplicate rows and redirect them to a separate table without
aborting the load.
While reviewing this TODO, I quickly noticed this idea closely aligns with
https://commitfest.postgresql.org/patch/4817.
Both ideas share common elements: allowing a user-specified table,
validating its metadata, and storing rows in it.
Based on that, I spent some time working on the implementation.
Proposed syntax:
COPY FROM (ONCONFLICT TABLE, CONFLICTTABLE conflict_tbl);
The CONFLICT_TABLE requires exactly four columns: COPY target table, COPY
filename, the line number of the duplicate, and the duplicate record itself.
This structure is fixed, a pre-defined data type is unnecessary. Validation is
based solely on the column data types (pg_attribute.atttypid) rather than their
names (pg_attribute.attname). The expected types are OID, TEXT, INT8, and TEXT,
respectively.
This uses INSERT ON CONFLICT infrastructure under the hood.
Demo:
table err_tbl1 ;CREATE TABLE t_copy_tbl(a int, b int, c text); CREATE TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text); CREATE UNIQUE INDEX ON t_copy_tbl (c); COPY t_copy_tbl(b,a, c) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose); 4,17,aaaaaa 6,11,aaaaaa 11,1,xxxxxxxx 12,1,xxxxxxxx 13,1,xxxxxxxx \.(3 rows)copy_tbl filename lineno line 18231 STDIN 2 6,11,aaaaaa 18231 STDIN 4 12,1,xxxxxxxx 18231 STDIN 5 13,1,xxxxxxxx
(I need to double-check the exclusion unique constraint)
Comments are welcome!
--
jian
https://www.enterprisedb.com/