Skip site navigation (1) Skip section navigation (2)

Re: Type incompatibilities converting char to uuid

From: Dirk Jagdmann <doj(at)cubic(dot)org>
To: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Type incompatibilities converting char to uuid
Date: 2008-12-18 06:52:35
Message-ID: 4949F333.1040704@cubic.org (view raw or flat)
Thread:
Lists: sfpug
> 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

In response to

Responses

sfpug by date

Next:From: Brian GhidinelliDate: 2008-12-18 16:09:59
Subject: Re: Type incompatibilities converting char to uuid
Previous:From: Josh BerkusDate: 2008-12-18 01:35:44
Subject: Re: Type incompatibilities converting char to uuid

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group