I have column "scopes" which is jsonb type and contains array of strings ["admin","agent"]. I want to transform it to enum array type. I am using postresql.
This is my migration.
CREATE TYPE enum_scopes AS ENUM ( 'owner', 'admin', 'agent', 'user' );
ALTER TABLE public.users ALTER COLUMN scopes TYPE enum_scopes[] USING scopes::text::enum_scopes[];
After i run migration i get this: MigrationError: malformed array literal: "["admin"]"
CodePudding user response:
You may first create a helper function like this
create or replace function jsonb_array_to_array(j jsonb) returns text[] as
$$
select array_agg(txt) from jsonb_array_elements_text(j) txt;
$$ language sql;
and then
ALTER TABLE public.users ALTER COLUMN scopes
TYPE enum_scopes[] USING jsonb_array_to_array(scopes)::enum_scopes[];
The helper function is useful and generic enough to be reused in other cases too.