Postgresql transform column type from jsonb to array type enum

Question

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"]"

Stefanov.sm · Accepted Answer · 2022-03-22 13:14:20Z

1

You may first create a helper function like this

create or replace function jsonb_array_to_array(j jsonb) returns text[] as
$$
  select case 
    when j is null then '{}' 
    else (select array_agg(txt) from jsonb_array_elements_text(j) txt)
  end;
$$ 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.

edited Mar 22, 2022 at 13:14

answered Mar 22, 2022 at 8:23

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Георги Кръстев Over a year ago

I am getting another error MigrationError: column "scopes" contains null values, Is there a way the column to be epmty array [] insted of null.

Stefanov.sm Over a year ago

Sure. See updated answer (the function definition).

Collectives™ on Stack Overflow

Postgresql transform column type from jsonb to array type enum

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related