question: can I do on conflict do nothing?

[MrSalman333]

I'm migration from mysql ,

the data is bad , we have data breaking Unique Constraint , I would like to do all copy operations with on conflict do nothing.

how can I do that?

[znorris]

Agreed. Supporting the on conflict clause in the configuration would make my life a lot easier and make this tool a lot more useful to me.

[dimitri]

Design proposal: WITH on conflict do nothing

What is being requested

A way to load data into a table that already contains rows — skipping any incoming row that would violate a unique or primary key constraint — rather than failing the entire batch.

Why the current pipeline cannot express this

pgloader uses PostgreSQL's COPY … FROM STDIN for bulk loading. COPY is all-or-nothing per batch: a single constraint violation rolls back the whole batch. There is no ON CONFLICT clause on COPY. The existing retry-batch! recovery path handles hard errors row-by-row but is designed for data-type failures, not intentional deduplication.

Proposed mechanism: staging table

-- 1. Stage rows with no constraints
CREATE TEMP TABLE _pgloader_tmp_<table>
    (LIKE target.table INCLUDING DEFAULTS);

COPY _pgloader_tmp_<table> (cols…) FROM STDIN;   -- never fails on conflicts

-- 2. Merge into real table, skip duplicates
INSERT INTO target.table
    SELECT * FROM _pgloader_tmp_<table>
    ON CONFLICT DO NOTHING;

-- 3. Discard staging table
DROP TABLE _pgloader_tmp_<table>;

INSERT … ON CONFLICT DO NOTHING is available since PostgreSQL 9.5. No version gating is required. The skipped-row count is directly observable: ROW_COUNT after the INSERT vs COUNT(*) in the staging table.

Foreign key constraints are not a concern here

Both v3 and v4 create foreign key constraints after the full load completes — they are post-load DDL, not present during COPY or during the INSERT … ON CONFLICT DO NOTHING step. If skipped rows cause an orphaned child row, that FK constraint simply fails to be reinstalled, which is already handled as a non-fatal warning by both implementations. No special FK handling is needed in this design.

Per-table vs global

The WITH clause today applies to the entire LOAD DATABASE command. The right default is global: if the data has duplicates in one table, the referencing tables almost certainly have the same issue. A per-table variant could be added later via ALTER TABLE tablename ON CONFLICT DO NOTHING inside the command, but the FK argument for doing so is weak given the post-load constraint reinstallation model.

Conclusion: implement as a single global WITH on conflict do nothing option.

MERGE as an alternative

PostgreSQL 15 introduced MERGE, which would allow expressing upsert semantics (WHEN MATCHED THEN UPDATE …) in addition to insert-or-skip. The staging table design is compatible with a future WITH on conflict do update variant using MERGE. Nothing in this design forecloses that path.

---

If you'd like to see this feature prioritised, sponsoring its development is possible through The Art of PostgreSQL OSS sponsorship programme. Sponsored features get dedicated implementation time and are tracked to completion; the design above is already in place and ready to go.