Restoring plain-text pg_dump with psql and --disable-triggers

I had to perform some tests with a short script to update some "legacy" data in one of my tables.

Cautious as I am, using an untested script, I decided to backup the relevant table before doing so. Simplest way to do that was:

pg_dump -a --file table.sql -t table database


Now I did what I had to do, checked the results and found them rather unsatisfactory. I thought to myself: how lucky I am to have a backup of that table.

I had already been warned when I backed up the table that:

pg_dump: NOTICE: there are circular foreign-key constraints among these table(s):
pg_dump: table
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.


I didn't think much of it, but now we have a problem. Indeed the table in question has multiple triggers attached to it, but I cannot restore the table.sql with option --disable-triggers of the pg_restore command.

If I try following command I get an error message:

pg_restore -a -d database -t table -h localhost --disable-triggers table.sql


namely:

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.


Is there a flag for the psql-command that exhibits the same behavior as --disable-triggers?

I have already checked the psql "manpage", searching for trigger and similar keywords but didn't find anything.

Or is the only option I have to drop the triggers on the table before restoring the data?

Sidenote: I am using postgres v. 9.3 on a Ubuntu 14.10 System

It was suggested to edit the generated sql-file, to include the statement:

ALTER TABLE table DISABLE TRIGGER ALL

When I now executed: psql -d database -f table.sql I got an error message about violating the "Unique" constraint of the primary key.

To fix this I tried to wrap the copy into:

```
BEGIN TRANSACTION READ WRITE;
TRUNCATE TABLE table;

-- copy here

@dezso had the completely right idea:


All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data

The only thing left now was to make it happen.

So here's what I did. I took a leaf out of his book and manually edited the dump-file to use a table named table_backup. Then I created said table using the definition provided in my pgAdmin (but it can be done manually, too).

I left out triggers and constraints, as well as Foreign Keys, and then proceeded to "updating" the original table with the data from the backup table like the following:

BEGIN TRANSACTION;
ALTER TABLE table DISABLE TRIGGER ALL;

UPDATE table SET 
    (column1, column2, ...) = 
    (table_backup.column1, table_backup.colum2, ...)
FROM table_backup WHERE table.pk_column = table_backup.pk_column;

ALTER TABLE table ENABLE TRIGGER ALL;
-- I didn't but you can drop table_backup here
COMMIT;

So I am finally back with my original data, ready for the next testrun ;)

StackExchange Database Administrators Q#96695, answer score: 4