Converting between UUID and VARCHAR

From: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
To: pgsql-sql(at)postgresql(dot)org
Subject: Converting between UUID and VARCHAR
Date: 2008-11-10 14:34:10
Message-ID: 49184662.3010708@megafon.hr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message A. Kretschmer 2008-11-10 14:44:42 Re: Converting between UUID and VARCHAR
Previous Message Mario Splivalo 2008-11-10 14:01:33 Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns