1

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

Striker
  • 67
  • 7
  • 2
    A properly normalized model without an enum and classic one-to-many relationship might be a better solution in the long run –  Aug 25 '22 at 07: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 Aug 25 '22 at 07:42

1 Answers1

3

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