Re: Alter column from text[] to uuid[]

On 06/11/2015 11:33 AM, Keith Rarick wrote:
> 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?

test=> alter table t alter u type uuid[] using u::uuid[];
ALTER TABLE
test=> select * from t;
                                       u

-----------------------------------------------------------------------------

{0289b709-3cd7-431c-bcbe-f942eb31b4c5,86cc14d6-7293-488e-a85f-384ae6773d28}
(1 row)

test=> \d t
       Table "public.t"
  Column |  Type  | Modifiers
--------+--------+-----------
  u      | uuid[] |



--
Adrian Klaver
adrian.klaver@aklaver.com