Convert postgres column type from enum to array of enums

Question

I currently have a table called employees having column access_level of type enum (accessLevel). But now I want to provide the employee multiple access_levels so now I need to convert the datatype from enum to array of enum.

I ran the following query

ALTER TABLE employees ALTER COLUMN access_level TYPE accessLevel[] USING access_level::accessLevel[]

But in response getting the following error
ERROR: cannot cast type accessLevel to accessLevel[] LINE 1: ... access_level TYPE accessLevel[] USING access_level::accessLev...

A properly normalized model without an enum and classic one-to-many relationship might be a better solution in the long run — user330315
– user330315, Commented Aug 25, 2022 at 7:25
@a_horse_with_no_name, I initially started with the same approach but then I realised this will end up creating too many rows so I stick to this methodology. It'll be great if you can help me understand the downsides of this approach — Striker
– Striker, Commented Aug 25, 2022 at 7:42

user330315 · Accepted Answer · 2022-08-25 07:25:05Z

4

You can't cast a single value to an array, you need to build a new array:

ALTER TABLE employees 
    ALTER COLUMN access_level TYPE accessLevel[] 
    USING array[access_level];

answered Aug 25, 2022 at 7:25

user330315

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

Collectives™ on Stack Overflow

Convert postgres column type from enum to array of enums

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related