PostgreSQL can't cast type json to character varying[]

Question

In my Rails 5.0.5 app I need to convert json column into string, array: true.

The values in my json columns are like:

[ "200px-RR5219-0015R.png", "2017_03_25_2235.doc", "137555.jpg" ]

I tried this migration:

class ChangeTaskAttachmentsTypeToString < ActiveRecord::Migration[5.0]
  def change
    change_column :tasks, :attachments, :string, array: true
  end
end

and got this error:

ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR:  column "attachments" cannot be cast automatically to type character varying[]
HINT:  You might need to specify "USING attachments::character varying[]".
: ALTER TABLE "tasks" ALTER COLUMN "attachments" TYPE character varying[]

Then I edited migration:

class ChangeTaskAttachmentsTypeToString < ActiveRecord::Migration[5.0]
  def change
    change_column :tasks, :attachments, 'character varying[] USING attachments::character varying[]' 
  end
end

And finally got this error:

PG::CannotCoerce: ERROR:  cannot cast type json to character varying[]
: ALTER TABLE "tasks" ALTER COLUMN "attachments" TYPE character varying[] USING attachments::character varying[]

How can I do this migration?

user_1995738 · Answer

I guess the array elements are filenames. If so, then you can remove all the characters []" and spaces and split the result to array, like this:

with my_table(attachments) as (
values
    ('[ "200px-RR5219-0015R.png", "2017_03_25_2235.doc", "137555.jpg" ]'::json)
)
select string_to_array(translate(attachments::text, '[] "', ''), ',')::varchar[]
from my_table;

                     string_to_array                     
---------------------------------------------------------
 {200px-RR5219-0015R.png,2017_03_25_2235.doc,137555.jpg}
(1 row)

so use:

... USING string_to_array(translate(attachments::text, '[] "', ''), ',')::varchar[]

A more formal (and general) solution would require a custom function, e.g.:

create or replace function json_to_text_array(json)
returns text[] language sql immutable as $$
    select array_agg(value)
    from json_array_elements_text($1)
$$;

that could be used in

alter table tasks alter column attachments type text[] 
    using json_to_text_array(attachments);

Note, I have used text[] as a more natural choice for Postgres but you can replace it with varchar[] if it matters.

Test it in Db<>fiddle.

PostgreSQL can't cast type json to character varying[]

1 Answers