PATCH: pg_dump to support "on conflict do update"

From: Tanin Na Nakorn <tanin47(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: PATCH: pg_dump to support "on conflict do update"
Date: 2025-05-04 05:47:53
Message-ID: CAAdYQOEUXnvte_FVHhyagoOV29cqp55qU3-30QbiF+pMUeMs6g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

Here's the patch (against the latest master) that will make pg_dump support
"on conflict do update" .

I've used this patch on v16 for our company's CI (on Github Actions), and
it works perfectly fine.

Users would be able to use it like this:

./src/bin/pg_dump/pg_dump $DATABASE_URL \
--table=some_random_table \
--data-only \

* --on-conflict-target-columns url,payload_checksum \
--on-conflict-update-clause='last_used_at=EXCLUDED.last_used_at' \*
--inserts \
--rows-per-insert=10 \
--no-sync \
--file=/tmp/test.dump

There are 3 caveats:

1. The "on conflict do update" would apply to every table. In my opinion,
this is fine. It's the user's choice if they want to apply it to one or all
tables. We could make the options more powerful (i.e. support multi-tables)
but it would add a lot of complexity.

2. -on-conflict-target-columns should have accepted a list of strings
instead. I'm working on it but I'd like an early review of the overall
patch first.

3. I can't figure out how to add a test for pg_dump. Any pointer would be
appreciated here.

Please help me review this patch as it's my first time submitting a patch
to Postgres.

Thank you!
Tanin

Attachment Content-Type Size
0001-pg_dump-to-support-on-conflict-update.patch application/octet-stream 5.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2025-05-04 09:03:07 Re: Fix slot synchronization with two_phase decoding enabled
Previous Message David G. Johnston 2025-05-04 05:27:36 Re: Make COPY format extendable: Extract COPY TO format implementations