Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux