Re: Type incompatibilities converting char to uuid

[Dirk Jagdmann <doj(at)cubic(dot)org>]

> I'm trying not to delete and recreate all of my foreign keys if I can
> help it for simplicity sake... any suggestions otherwise?

Not really, but what is the problem with droping the foreign key
constraint before column conversion? Do you have GB of UUIDs stored and
fear that the index recreation might take long?

Just as Josh has suggested, I just tried the following and it seems to work:

begin;
alter table uuidtest_ref drop constraint uuidtest_ref_uidtest_fkey;
ALTER TABLE uuidtest ALTER COLUMN uidTest TYPE uuid USING
CAST(regexp_replace(uidTest, '([A-Z0-9]{4})([A-Z0-9]{12})', E'\\1-\\2')
AS uuid);
ALTER TABLE uuidtest_ref ALTER COLUMN uidTest TYPE uuid USING
CAST(regexp_replace(uidTest, '([A-Z0-9]{4})([A-Z0-9]{12})', E'\\1-\\2')
AS uuid);
alter table uuidtest_ref add constraint uuidtest_ref_uidtest_fkey
foreign key (uidTest) references \
uuidtest(uidTest) ON UPDATE CASCADE ON DELETE CASCADE;
commit;


-- 
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org