Thread: Alter column from text[] to uuid[]
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?
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
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
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.