Converting between UUID and VARCHAR

Поиск
Список
Период
Сортировка
От Mario Splivalo
Тема Converting between UUID and VARCHAR
Дата
Msg-id 49184662.3010708@megafon.hr
обсуждение исходный текст
Ответы Re: Converting between UUID and VARCHAR  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Список 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Mario Splivalo
Дата:
Сообщение: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns
Следующее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Converting between UUID and VARCHAR