如何在PostgreSQL中从SQL查询创建枚举类型？

Question

我希望从PostgreSQL的一列不同值中创建一个枚举。我想要使用查询来获取该类型的所有可能值，而不是从所有标签中创建枚举。我期望的代码如下：

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

但我没有被允许这样做。有没有任何方法可以实现这个目标？

- user1219801

---

针对这个问题，你真的需要使用枚举吗？将外键关联到“genres（类型）”表不是同样可行吗？ - mu is too short

可能OP想要一个常量枚举，与更改表中的内容无关。FK在大多数情况下都是一个好的解决方案，但并非总是如此。 - Angelo Fuchs

Answer 1

如果类型种类是动态的，即您会不时地创建新类型并重命名旧类型，或者如果您希望将附加信息保存在每个类型中，则值得考虑 @mu的建议 和@Marcello的实现 - 除此之外，您应该只使用类型text而不是varchar(20)，并考虑使用ON UPDATE CASCADE来进行fk约束。

除此之外，这是您要求的步骤：

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

您需要使用动态SQL来完成这个任务。简单的方法是使用DO命令（适用于PostgreSQL 9.0+版本）。
请确保您的字符串已经通过quote_literal()进行了正确转义。
我使用string_agg()函数（适用于PostgreSQL 9.0+版本）来聚合字符串。

- Erwin Brandstetter

Answer 2

我认为这是有意而为之，你做不到。

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

枚举类型是由一组静态有序的值组成的数据类型。

您在SELECT子句中使用的“DISTINCT”关键字让我觉得您的模式不是完全规范化的。

例如：

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

SELECT DISTINCT genre FROM movies;

应该变成：

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;

- Marcello Romani

Answer 3

可以在pgsql中完成。以下为内联代码实现:

DO $$
  DECLARE temp text;
BEGIN
    SELECT INTO temp string_agg(DISTINCT quote_literal(genre),',') FROM movies;
    EXECUTE 'CREATE TYPE foo AS ENUM ('||temp||')';
END$$;

{{链接1：fiddle}}

- Jakub Kania

---

在 string_agg() 之外使用 DISTINCT 可能是一个打字错误？ - Erwin Brandstetter

@ErwinBrandstetter 不只是一个小笔误，我忘记了它然后当我看到 op 使用它时才将其放在某个地方。谢谢。 - Jakub Kania

Answer 4

一个简单的方法是在客户端执行SELECT，然后从中复制名称。

我认为这就是你能做的所有事情。如果你查看文档http://www.postgresql.org/docs/9.3/static/sql-createtype.html，你会注意到写着CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] )，所以不在那里的关键字是expression。这意味着在ENUM之后可能没有表达式，而只有标签列表。

您可能想遵循@muistooshort的建议，创建一个流派表（您可以使用INSERT ... SELECT ...填充），然后创建一个指向该表的外键。

- Angelo Fuchs