PostgreSQL: restore data only if not exist

Question

I am dumping the data of a table into a .tar file and restoring it into the same table on a different database.

C:\Program Files\PostgreSQL\12\bin\pg_dump.exe --file "C:\\SQL_ST~1\\pg_dump.tar" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs --data-only --table "runtime.batches" "sdm_kunde"

C:\Program Files\PostgreSQL\12\bin\pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "sdm_wir" --data-only --verbose --schema "runtime" --table "batches" "C:\\SQL_ST~1\\pg_dump.tar"

The problem is that the databases have almost identical information stored inside them, which conflicts with the restore due to the unique restraint on one of the columns. Is there a way to only restore data if it doesn't exist in the target database?

Answer 1

If you have a primary key or unique constraint on your tables that will identify the duplicates, you can

pg_dump --format=custom --inserts --on-conflict-do-nothing --data-only --blobs --table=mytab mydb

Restoring that dump will silently skip rows that are already in the target table. Note that large objects will be unconditionally replaced — but then, you shouldn't be using large objects anyway.