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.
