Postgres Pro
Downloads
Home   >   mailing lists

Thread: Converting between UUID and VARCHAR

Converting between UUID and VARCHAR

From
Mario Splivalo
Date:
I have a table, like this:

CREATE TABLE t1
(  u1 character varying
)

And some data inside:

INSERT INTO t1 (u1) VALUES ('62d6b434-7dfd-4b3b-b1bf-87f6c20c10dd');
INSERT INTO t1 (u1) VALUES ('e3fee596-164b-4995-9e0d-7b2a79e83752');
INSERT INTO t1 (u1) VALUES ('37a42ec8-9000-44bc-bb06-13b5d4373a45');
INSERT INTO t1 (u1) VALUES ('fe160c33-846b-4843-999e-071cbc71260c');
INSERT INTO t1 (u1) VALUES ('4a8d9697-f26c-41a4-91cd-444226e075f7');
INSERT INTO t1 (u1) VALUES ('e21cddf9-9843-42a0-acb6-95933ed2d6ee');
INSERT INTO t1 (u1) VALUES ('b3c04c2d-3706-4fa2-a3f5-b15552eaaadb');
INSERT INTO t1 (u1) VALUES ('e73d128d-fcf4-427c-959e-ac989150f2c4');
INSERT INTO t1 (u1) VALUES ('c88ac916-efb6-4afe-a2e3-8f2f49316c67');
INSERT INTO t1 (u1) VALUES ('2014ab62-bee9-4a3a-b273-58859d1d8941');


Now, I can do this:

SELECT u1::uuid FROM t1;

But I can't do this:

ALTER TABLE t1 ALTER u1 TYPE uuid;

So, if I want to change column t1 to use uuid type instead of varchar I 
need to do this:

SELECT u1::uuid INTO _t1 from t1;
DROP TABLE t1;
ALTER TABLE _t1 RENAME TO t1;

That's pain in the behind if I have several tables referenced with 
foreign keys and tons of data.

Is there a more 'elegant' way of changing varchar data type to uuid?

My database consists of several tables which heavily use UUIDs, but 
since we were on 8.2 we used varchar (actually char(36)), but now pg8.3 
supports UUID, and I need to 'upgrade' my database to use that new time.
Mike

Re: Converting between UUID and VARCHAR

From
"A. Kretschmer"
Date:
am  Mon, dem 10.11.2008, um 15:34:10 +0100 mailte Mario Splivalo folgendes:
> I have a table, like this:
> 
> CREATE TABLE t1
> (
>   u1 character varying
> )
> 
> And some data inside:
> 
> INSERT INTO t1 (u1) VALUES ('62d6b434-7dfd-4b3b-b1bf-87f6c20c10dd');
> INSERT INTO t1 (u1) VALUES ('e3fee596-164b-4995-9e0d-7b2a79e83752');
> 
> Now, I can do this:
> 
> SELECT u1::uuid FROM t1;
> 
> But I can't do this:
> 
> ALTER TABLE t1 ALTER u1 TYPE uuid;

test=# CREATE TABLE t1
test-# (
test(#   u1 character varying
test(# )
test-# ;
CREATE TABLE
test=# INSERT INTO t1 (u1) VALUES
('62d6b434-7dfd-4b3b-b1bf-87f6c20c10dd');
INSERT 0 1
test=# alter table t1 alter column u1 type uuid using u1::uuid;
ALTER TABLE
test=# \d t1;    Table "public.t1"Column | Type | Modifiers
--------+------+-----------u1     | uuid |



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Converting between UUID and VARCHAR

From
"Helio Campos Mello de Andrade"
Date:
Didn't you create those other tables with "character varying" too? If
you did i think that you need to change those tables too.

On Mon, Nov 10, 2008 at 12:44 PM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
> am  Mon, dem 10.11.2008, um 15:34:10 +0100 mailte Mario Splivalo folgendes:
>> I have a table, like this:
>>
>> CREATE TABLE t1
>> (
>>   u1 character varying
>> )
>>
>> And some data inside:
>>
>> INSERT INTO t1 (u1) VALUES ('62d6b434-7dfd-4b3b-b1bf-87f6c20c10dd');
>> INSERT INTO t1 (u1) VALUES ('e3fee596-164b-4995-9e0d-7b2a79e83752');
>>
>> Now, I can do this:
>>
>> SELECT u1::uuid FROM t1;
>>
>> But I can't do this:
>>
>> ALTER TABLE t1 ALTER u1 TYPE uuid;
>
> test=# CREATE TABLE t1
> test-# (
> test(#   u1 character varying
> test(# )
> test-# ;
> CREATE TABLE
> test=# INSERT INTO t1 (u1) VALUES
> ('62d6b434-7dfd-4b3b-b1bf-87f6c20c10dd');
> INSERT 0 1
> test=# alter table t1 alter column u1 type uuid using u1::uuid;
> ALTER TABLE
> test=# \d t1;
>     Table "public.t1"
>  Column | Type | Modifiers
> --------+------+-----------
>  u1     | uuid |
>
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
Helio Campos Mello de Andrade