Alter column type to enum with mapping

user_1772830 0
Viewed 605

I have a nullable boolean column my_column.

I have enum type called my_enum with 3 values (ENABLED/DISABLED/INHERITED).

I want to change this column data as follows:

  • TRUE becomes ENABLED
  • FALSE becomes DISABLED
  • NULL becomes INHERITED

Is there a way to provide the mapping in a single alter table statement?

Schematically I'd want something like this:

ALTER TABLE my_table
  ALTER COLUMN my_column TYPE my_type USING (
    {
      TRUE => ENABLED,
      FALSE => DISABLED,
      NULL => INHERITED
    }
  );
alter-table postgresql sql
2 Answers

You can use a case expression, with appropriate casting:

alter table my_table
    alter column my_column set data type my_type using (
        case my_column 
            when true  then 'enabled'::my_type
            when false then 'disabled'::my_type
            else            'inherited'::my_type
        end
    )
;

Demo on DB Fiddlde:

Initial content of the table:

id | my_column
-: | :--------
 1 | t        
 2 | f        
 3 | null

After executing the alter table statement:

id | my_column
-: | :--------
 1 | enabled  
 2 | disabled 
 3 | inherited
user_106767160

Solution is apparently pretty simple (thanks to @Bergi):

      ALTER TABLE my_table
        ALTER COLUMN my_column TYPE my_type USING (
          CASE my_column
          WHEN TRUE  THEN 'ENABLED'::my_type
          WHEN FALSE THEN 'DISABLED'::my_type
          ELSE            'INHERITED'::my_type
          END
        );
user_17728300
Related