CODEWITHSUNDEEP
sqlpostgresqlalter-table
Andrey Deineko
Andrey Deineko

Reputation: 52367

Alter column type to enum with mapping

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:

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
    }
  );

Upvotes: 2

Views: 1723

Answers (2)

Andrey Deineko
Andrey Deineko

Reputation: 52367

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

Upvotes: 1

GMB
GMB

Reputation: 222672

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

Upvotes: 1

Related Questions