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
