How to create enum from a SQL query in PostgreSQL?

Question

I want to create an enum from distinct values of a column in PostgreSQL. Instead of creating an enum from all labels, I want to create it using a query to get all possible values of the type. I am expecting something like:

CREATE TYPE genre_type AS ENUM
   (select distinct genre from movies);

But I am not allowed to do this. Is there any way to achieve this?

Answer 1

If genre types are in any way dynamic, i.e. you create new ones and rename old ones from time to time, or if you want to save additional information with every genre type, @mu's advice and @Marcello's implementation would be worth considering - except you should just use type text instead of varchar(20) and consider ON UPDATE CASCADE for the fk constraint.

Other than that, here is the recipe you asked for:

DO
$$
BEGIN
EXECUTE (
    SELECT format('CREATE TYPE genre_type AS ENUM (%s)'
                  ,string_agg(DISTINCT quote_literal(genre), ', '))
    FROM  movies);
END 
$$

You need dynamic SQL for that. The simple way is a DO command (PostgreSQL 9.0+).
Make sure your strings are properly escaped with quote_literal().
I aggregate the string with string_agg() (PostgreSQL 9.0+).

Answer 2

I think you can't do that by design.

http://www.postgresql.org/docs/9.1/static/datatype-enum.html

Enumerated (enum) types are data types that comprise a static, ordered set of values.

The "DISTINCT" keyword in your SELECT clause makes me think your schema is not fully normalized.

For example:

CREATE TABLE movies(
    ...
    genre VARCHAR(20)
    ...
);

SELECT DISTINCT genre FROM movies;

Should become:

CREATE TABLE genres(
    id SERIAL PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE movies (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    genre INTEGER REFERENCES genres(id)
);

SELECT name FROM genres;