How to make pg_dump generate upsert statements in Postgres 9.5

Question

I want to dump a database and restore dump on another database that could have data in it. How do I make pg_dump generate insert on conflict update statements instead of just insert statements? I use Postgres 9.5

Answer 1

With PostgreSQL 12 and later, you can use --on-conflict-do-nothing switch of pg_dump. See docs.

Example:

$ pg_dump test --table=lifehack --section=data --column-inserts --on-conflict-do-nothing

[...]

--
-- Data for Name: lifehack; Type: TABLE DATA; Schema: public; Owner: filip
--

INSERT INTO public.lifehack (id, content) VALUES (1, 'Brush your teeth twice a day.') ON CONFLICT DO NOTHING;


--
-- PostgreSQL database dump complete
--