CODEWITHSUNDEEP
sqlpostgresqlpsqlpg-dumppg-restore
Taylor F
Taylor F

Reputation: 101

pg_restore clean table psql

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?

Upvotes: 2

Views: 4061

Answers (1)

bbuckley123
bbuckley123

Reputation: 1979

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

Upvotes: 2

Related Questions