How to change the data type of a table column to enum?

Question

I have a table "ENGINE" in a Postgres DB, in which I have to change the datatype of a column named loglevel from string to enum.

However, I am not able to understand instructions in the manual:

ALTER [ COLUMN ] column TYPE type [ USING expression ]

Answer 1

enum is not a data type per se. It's a class of data types. (Generally, you can replace any enum with a FK column to a 1:n table.) And "string" is not a data type at all in Postgres. I assume we are talking about text or varchar? If so, these are the steps for your task:

1.) Create new enum type if it does not exist, yet. Let's call it loglevel. It has to include all distinct values from your string column or the type cast will fail. This DO command takes care of it:

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 have to exclude NULL if present, that cannot be listed as enum value. (Any enum type can be NULL anyway.)

Related:

• Algebraic Data Types in Postgres

2.) Change the data type of the column:

You have to specify USING loglevel::loglevel explicitely, since there is no implicit cast between any string type (text, varchar?) and the new enum data type:

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

Details:

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