Simple tool to dump a Postgresql table as INSERT statements with ON CONFLICT clause (also known as "upsert" statements).
Pre-alpha software. Expect crashes, data loss, silent data corruption etc.
The pg_dump
command can dump tables as INSERT statements however you can't directly
restore such dumps if the database has conflicting rows. Furthermore pg_dump
is doing more work than simply querying the data and this sometimes causes
seemingly unrelated failures.
$ go install github.com/tomyl/pg-dump-upsert@latest
$ pg-dump-upsert -h
Usage of pg-dump-upsert:
-conflict-column string
Append an ON CONFLICT clause for this column. All other columns will be included in a DO UPDATE SET list.
-dsn string
Connection string. Example: postgres://user:password@host:5432/db
-from string
Source table to dump.
-insert-columns string
Comma-separated list of columns to include in INSERT statement. Defaults to all columns.
-noconflict
Append ON CONFLICT DO NOTHING.
-query string
Use custom SELECT query. By default fetches all rows. Note that column order must match -insert-columns. It is also valid to just specify a WHERE clause. It will be appended to the default query.
-to string
Table name to use in INSERT statements. Defaults to the source table.
-tx
Wrap INSERT statements in transaction.
-verbose
Log query statement to stderr.Dump all rows in table employee:
$ pg-dump-upsert -dsn "postgres://user:password@host:5432/db" -from employee
INSERT INTO employee (id, created_at, name, salary) VALUES (1, '2018-06-13 21:10:34.769555+08', 'Jane Doe', 123456);
...Choose which columns to dump:
$ pg-dump-upsert -dsn "postgres://user:password@host:5432/db" -from employee -insert-columns id,name
INSERT INTO employee (id, name) VALUES (1, 'Jane Doe');
...Ignore conflicts:
$ pg-dump-upsert -dsn "postgres://user:password@host:5432/db" -from employee -noconflict
INSERT INTO employee (id, created_at, name, salary) VALUES (1, '2018-06-13 21:10:34.769555+08', 'Jane Doe' 123456) ON CONFLICT DO NOTHING;
...Update columns on conflict:
$ pg-dump-upsert -dsn "postgres://user:password@host:5432/db" -from employee -conflict-column id
INSERT INTO employee (id, created_at, name, salary) VALUES (1, '2018-06-13 21:10:34.769555+08', 'Jane Doe', 123456) ON CONFLICT (id) DO UPDATE SET created_at=EXCLUDED.created_at, name=EXCLUDED.name;
...Fetch a subset of the rows:
$ pg-dump-upsert -dsn "postgres://user:password@host:5432/db" -from employee -query "WHERE salary > 12345"
INSERT INTO employee (id, created_at, name, salary) VALUES (1, '2018-06-13 21:10:34.769555+08', 'Jane Doe', 123456);
...Use a different table name in the INSERT statements:
$ pg-dump-upsert -dsn "postgres://user:password@host:5432/db" -from employee -to minions
INSERT INTO minions (id, created_at, name, salary) VALUES (1, '2018-06-13 21:10:34.769555+08', 'Jane Doe', 123456);
...To restore a dump, simply use the \i command in psql.
- Implement support for all Postgres data types.
- Skip generated columns when dumping views.
- Allow which columns to update when specifying
-conflict-column? - More unit tests would be nice...
- Finish this TODO list.