Postgresql – Replace content of only one table with new version (should disable foreign keys?)

backupforeign keymigrationpg-dumppostgresql

I have 2 tables. 2nd has foreign keys to reference 1st.
I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and foreign keys.
I need a generic way to do it for any table so no exact name of foreign keys or constraints or columns may be "hardcoded" (but script "generation" like pg_dump does is ok).

I can't just truncate 1st table and insert everything from the new version: pg doesn't allow to delete referenced rows.

I tried to use deferred like this (I planned to automatically append it to pg_dump generated sql):

START TRANSACTION;

SET CONSTRAINTS ALL DEFERRED;

DELETE FROM X;

// ORIGINAL PART OF GENERATED SCRIPT
INSERT INTO X VALUES (10, ....)

COMMIT;

but I see Key (id)=(10) is still referenced from table.

The constraint:

CONSTRAINT second_fkey FOREIGN KEY (X) REFERENCES first (id) MATCH SIMPLE 
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY

Best Answer

The deferred constraints solution (see an example in the question) actually works if I make the keys deferrable in normal way.

This didn't work because I did UPDATE pg_catalog.pg_constraint SET condeferrable = true; but forgot to set the same for pg_catalog.pg_trigger.

Related Solutions

Sql-server – What alternatives exist when a table requires too many foreign keys

If there's any way to group parts, you might be able to introduce intermediate tables as a workaround. This won't work.

Parts
+ Table 1
+ Table 2
+ ...
+ Table 400

But something along these lines might.

Parts
+ RedOrangeYellow parts
  + Table 1
  + Table 2
  + ...
  + Table 200

+ GreenBlueIndigoViolet parts
  + Table 201
  + Table 202
  + ...
  + Table 400

I'd want to take a hard look at your DDL before I recommended doing this, though. And if you do this, don't start throwing ID numbers all over the place. You ought to be able to join "Table 400" directly to "Parts" without including "GreenBlueIndigoViolet parts".

Postgresql – use `on delete set null` on a compound foreign key which contains a primary key column

You can't use on delete set null if any of the foreign key columns don't accept null:

create schema stack;
set search_path=stack;
--
create table t1( foo integer
               , bar integer
               , primary key(foo,bar) );
--
create table t2( foo integer
               , baz integer
               , bar integer
               , primary key (foo,baz)
               , foreign key (foo,bar) references t1 on delete set null );
--
insert into t1 values (1,1);
insert into t2 values (1,1,1);
delete from t1;
/*
ERROR:  null value in column "foo" violates not-null constraint
DETAIL:  Failing row contains (null, 1, null).
CONTEXT:  SQL statement "UPDATE ONLY "postgres"."t2" SET "foo" = NULL, "bar" = NULL WHERE $1 OPERATOR(pg_catalog.=) "foo" AND $2 OPERATOR(pg_catalog.=) "bar""
*/
--
drop schema stack cascade;

And the same is true for on delete set default. I don't know of any simple way round this, and I'm not sure I agree that it is the most sensible behaviour, I think a better choice for on delete set null would be to set the nullable columns to null if there are both.

Best Answer

Related Solutions

Sql-server – What alternatives exist when a table requires too many foreign keys

Postgresql – use `on delete set null` on a compound foreign key which contains a primary key column

Related Question