sql - 规范化逗号分隔的外键

Question

我有“element”和“authors”表，其中 element 表可以有一个或多个作者，但表没有规范化(有些人很久以前就设计了这个)。例如:

element_id   ...     ...  authors
369561                    319,30,2496,544

数据库足够大，所以我需要一些SQL 脚本 来创建和填充连接表，例如:“元素作者”

element_id | author_id
369561       319
369561       30
369561       2496
369561       544

我不知道如何编写这个脚本，有什么帮助吗？

Answer 1

总体思路是使用 regexp_split_to_table 将逗号分隔的字段拆分为一个集合，将每个值转换为 integer，并将结果与​​ element_id 配对 从元组中我们得到了原始的逗号分隔字段。

对于 PostgreSQL 9.3，您将编写:

INSERT INTO element_authors(element_id, author_id)
SELECT
  element_id,
  CAST (author_id AS integer) AS author_id
FROM
  element,
  LATERAL regexp_split_to_table(nullif(authors, ''), ',') author_id;

或者在较旧的 PostgreSQL 版本上，我认为在这种情况下可以安全地编写:

INSERT INTO element_authors(element_id, author_id)
SELECT
  element_id,
  CAST( regexp_split_to_table(nullif(authors, ''), ',') AS integer) AS author_id
FROM
  element;