Home pg_restore clean table psql
Reply: 1

pg_restore clean table psql

Taylor F
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 Reply to 2017-12-08 14:23:42Z

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 <options> -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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.365472 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO