I’m using Postgres 9.5 on Mac Sierra. I want to export some records from my local db and import them into a db on a Linux machine (also running PostGres 9.5). I’m using this command to export data from my local machine …
localhost:myproject davea$ pg_dump -U myproject mydb -a -t table1 -t table2 -t table3 > /tmp/pgdata.sql
The data is exported in a series of copy commands. Is there a way to export the table data so that the file has a bunch of “INSERT … ON CONFLICT DO NOTHING;” statements? There are some duplicates in the origin database and the destination db but I don’t want that to derail the import of the non-duplicate data.
Best Answer
pg_dump
won't allow you to do exactly what you ask for, but you have an option that might be good enough. According to the documentation of pg_dump, you have the--inserts
option:For instance, let's assume you use
myhost
andmydb
.We create and populate one table (in one schema):
At this point, we back it up:
After the backup, we delete one of the rows at the table, but we still leave one:
At this point, we do restore the backup (this is what you would normally do on your second database), and get the following messages:
The restore process generated one error (the row that was already on the table), but did insert the rest of the data.
Although this is not exactly what you were asking for, for all practical purposes you achieve the result you're looking for.