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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Jun 11, 2015 at 12:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

Got it. Thanks!

I feel silly not to have tried that. Somehow I interpreted
"cannot be cast automatically to type uuid[]" as meaning
that it cannot be cast at all (and would therefore need a
more involved expression to specify how to convert it),
rather than that the cast merely needed to be explicit.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general