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