sql - 规范逗号分隔的外键

我有“element”和“authors”表，其中element表可以有一个或多个authors，但是表没有被规范化（有些人早就设计好了）。如：

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

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

element_id | author_id
369561       319
369561       30
369561       2496
369561       544

我不知道怎么写这个剧本，有什么帮助吗？

最佳答案

一般的想法是使用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;

关于sql - 规范逗号分隔的外键，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22655631/