Convert postgres column type from enum to array of enums-CodePudding

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

CodePudding user response：

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