CODEWITHSUNDEEP
sqlpostgresql
Jessie Liauw A Fong
Jessie Liauw A Fong

Reputation: 200

Disable ALL checks on foreign keys when migrating Postgresql

For the last couple of days I have migrated from MySQL to Postgres. But without any success. I have now ported my MySQL database to Postgres but I was using UUID's in MySQL in a VARCHAR. Now I want to go to postgres but it seems impossible for me to transfer the varchar in postgres to UUID. Because the database is not empty there are foreign key constraints. I thought I could turn them all off with:

set session_replication_role to replica;

I also tried to turn off all triggers for the tables. For example, this is the query I try to run:

ALTER TABLE building_block_buildingblock DISABLE TRIGGER ALL;
ALTER TABLE building_block_buildingblockhistory DISABLE TRIGGER ALL;
ALTER TABLE building_block_buildingblockhistory ALTER COLUMN id SET DATA TYPE UUID USING (uuid_generate_v4());
ALTER TABLE building_block_buildingblock ALTER COLUMN id SET DATA TYPE UUID USING (uuid_generate_v4());
ALTER TABLE building_block_buildingblockhistory ALTER column building_block_id SET DATA TYPE UUID USING (uuid_generate_v4());

But I get the error:

SQL Error [42804]: ERROR: foreign key constraint "building_block_buildingbl_building_block_id_e08cbc73_fk" cannot be implemented
  Detail: Key columns "building_block_id" and "id" are of incompatible types: character varying and uuid.

I also tried wrapping it in a function as shown here: PostgreSQL - disabling constraints

This is the function I tried:

CREATE OR REPLACE FUNCTION f() RETURNS void AS
$BODY$
begin
set session_replication_role to replica;
SET CONSTRAINTS ALL deferred;
ALTER TABLE building_block_buildingblock DISABLE TRIGGER ALL;
ALTER TABLE building_block_buildingblockhistory DISABLE TRIGGER ALL;
ALTER TABLE building_block_buildingblockhistory ALTER COLUMN id SET DATA TYPE UUID USING (uuid_generate_v4());
ALTER TABLE building_block_buildingblock ALTER COLUMN id SET DATA TYPE UUID USING (uuid_generate_v4());
ALTER TABLE building_block_buildingblockhistory ALTER column building_block_id SET DATA TYPE UUID USING (uuid_generate_v4());
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
select f();

Is there a way to do this migration I want to do? The only thing I can think of now is manually adjusting my SQL dump file. But that does not only sound like a lot of work but also error prone.

Upvotes: 0

Views: 3373

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

The following will certainly disable all foreign keys:

SET session_replication_role TO replica;

It can only be run as a superuser.

There is one exception: a trigger on which you used

ALTER TABLE ... ENABLE REPLICA|ALWAYS TRIGGER ...

will fire even if session_replication_role is set to replica.

Upvotes: 0

Related Questions