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