How do I change the datatype of a table column to enumeration?

enum

is not a data type per se. It is a data type class. (Typically, you can replace any enum

FK column with a 1: n table.) And "string" is not a data type at all in Postgres. I guess we are talking about text

or varchar

? If so, these are the steps for your task:

1.) Create a new type enum

if it doesn't already exist. Let's call him loglevel

. It must include all the different values ​​from your row column, or there is no casting. This command DO

will take care of this:

DO
$$
BEGIN
EXECUTE (
   SELECT 'CREATE TYPE loglevel AS ENUM (' 
        || string_agg(quote_literal(loglevel), ', ')
        || ')'
   FROM  (
      SELECT loglevel
      FROM   "ENGINE"
      WHERE  loglevel IS NOT NULL  -- exclude NULL if present
      GROUP  BY 1
      ORDER  BY 1
      ) sub
   );
END
$$;

      

        
        
        
      

    

We must exclude NULLs if they are present, which cannot be listed as an enumeration value. (Any type enum

can be NULL anyway.)

on this topic:

• Algebraic data types in Postgres

2.) Change the data type of the column:

You must specify explicitly USING loglevel::loglevel

, since there is no implicit transfer between any string type ( text

, varchar

?) And the new data type enum

:

ALTER TABLE "ENGINE" ALTER loglevel TYPE loglevel USING loglevel::loglevel;

      

        
        
        
      

    

More details:

• Rails Migrations: tried to change column type from string to integer