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

Keith Rarick <kr@xph.us> writes:
> 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

It wants you to do this:

alter table t alter u type uuid[] using u::uuid[];

The original command worked without a USING because anything-to-text is
considered an allowable assignment coercion; but the other way around
requires an explicit cast.

> 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?)

No, the error message is just worded carelessly; it's the same whether or
not you said USING.  Probably when there's a USING it needs to be worded
more like
ERROR:  result of USING clause cannot be cast automatically to type uuid[]

            regards, tom lane