Home pg_restore clean table psql

# pg_restore clean table psql

Taylor F
1#
Taylor F Published in 2017-12-07 19:00:43Z
 I am trying to restore a table I dumped to its current location. The table was dumped using: pg_dump -t table db > table.sql -U username  Trying to restore, I'm using: pg_restore -c --dbname=db --table=table table.sql  I am using -c since the table currently exists and has data, however it returns: pg_restore: [archiver] input file appears to be a text format dump. Please use psql.  I've also tried: -bash-4.2\$ psql -U username -d db -1 -f table.sql  But since the data is there already and there's no --clean option for this psql command (I believe), it returns: psql:table.sql:32: ERROR: relation "table" already exists  Is there a way to use pg_restore correctly or use psql with a --clean option?
JGH
2#
 Bad news for you, friend. You can’t pg_restore from a plan-text dump. It’s in the docs: “pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats.” https://www.postgresql.org/docs/9.6/static/app-pgrestore.html If you can re-execute pg_dump, you can do so with the -Fc flag, which will produce an artifact that you can restore with pg_restore. If you are stuck with the table.sql in plain-text format, you do have a few options (I’m calling your target table my_fancy_table in the two examples below): Option 1: Drop the table: drop table my_fancy_table; Now run your psql command: psql -f table.sql If you have referential integrity where other tables necessitate rows being there, you might not be able to drop the table. Option 2: Upsert from a temp table You can edit the sql file (since it’s in plain text) and change the table name to my_fancy_table_temp (or any table name that does not yet exist). Now you'll have two tables, a temp one from the sql file and the real one that's been there the whole time. You can then write some upsert SQL to insert or update the real table with the rows from the temp table like so: insert into my_facy_table (column1, column2) select column1, column2 from my_fancy_table_temp on conflict (my_fancy_unique_index) update my_fancy_table set column1 = excluded.column1, column2 = excluded.column2