CODEWITHSUNDEEP
sqljsonpostgresqlalter-table
PRAFULL PRAJAPATI
PRAFULL PRAJAPATI

Reputation: 13

Convert json to text[] in alter table

ALTER TABLE students
ALTER COLUMN hobbies type text[] using hobbies::text[];

It is showing an error that json cannot be cast into text[].

Upvotes: 1

Views: 479

Answers (1)

jjanes
jjanes

Reputation: 44305

You need a helper function to do that.

create function json_to_array(json) returns text[] language sql as $$ 
    select array_agg(x) from json_array_elements_text($1) f(x) 
$$;

ALTER TABLE students
ALTER COLUMN hobbies type text[] using json_to_array(hobbies);

Upvotes: 1

Related Questions