Re: How to replace rows in table so that foreign key rows

Andrus wrote:

> Is there any generic way to turn off foreign key constraints before delete
> command in transaction ?

From TFM: "--disable-triggers
This option is only relevant when performing a data-only restore. It
instructs pg_restore to execute commands to temporarily disable triggers
on the target tables while the data is reloaded. Use this if you have
referential integrity checks or other triggers on the tables that you do
not want to invoke during data reload.

Presently, the commands emitted for --disable-triggers must be done as
superuser. So, you should also specify a superuser name with -S, or
preferably run pg_restore as a PostgreSQL superuser."

So, you could use this option with pg_dump/pg_restore, and look at the
"commands to temporarily disable triggers" it produces.

I did so, and for a table named 'country' the following SQL statements
were produced:

-- Disable triggers
UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'country'::pg_catalog.regclass;

/* COPY command goes here to bulk load table data. */

-- Enable triggers
UPDATE pg_catalog.pg_class SET reltriggers = (SELECT pg_catalog.count(*)
FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid =
'country'::pg_catalog.regclass;

Regards,
Berend Tober