Alter column from text[] to uuid[]

From: Keith Rarick <kr(at)xph(dot)us>
To: pgsql-general(at)postgresql(dot)org
Subject: Alter column from text[] to uuid[]
Date: 2015-06-11 18:33:16
Message-ID: CAD25zGDiaqCG=eqXp=byvZcdgvcquBh7KBpJpJQseSPOwfvhiw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table:

kr=# create table t (u uuid[]);
CREATE TABLE
Time: 3.742 ms
kr=# insert into t values
('{"0289b709-3cd7-431c-bcbe-f942eb31b4c5","86cc14d6-7293-488e-a85f-384ae6773d28"}');
INSERT 0 1
Time: 1.735 ms

I recently did the following:

kr=# alter table t alter u type text[];
ALTER TABLE
Time: 5.513 ms

Now I'd like to put it back the way it was, but my attempts didn't work:

kr=# alter table t alter u type uuid[];
ERROR: column "u" cannot be cast automatically to type uuid[]
HINT: Specify a USING expression to perform the conversion.
Time: 0.244 ms
kr=# alter table t alter u type uuid[] using array(select unnest(u));
ERROR: cannot use subquery in transform expression
Time: 0.299 ms

I noticed that when I inserted a record into the table, postgres was able
to convert my input string to uuid[] with no problem, so I tried simulating
that, but to no avail:

kr=# alter table t alter u type uuid[] using array_to_string(u,',');
ERROR: column "u" cannot be cast automatically to type uuid[]
HINT: Specify a USING expression to perform the conversion.
Time: 0.321 ms

(Interestingly, postgres seems to think I don't even have a USING clause
here. Could there be some optimization that removed it?)

I'm not sure what to do here. Can someone help me?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Asma Riyaz 2015-06-11 18:44:38 Re: GCC error and libmpfr.so.4 not found
Previous Message Nguyen, Phuonglan H. 2015-06-11 18:01:08 pg_bulkload