如何将含多邮箱的列拆分到新表并创建关联映射表？

问题背景 #

原本图省事在表中用单个字段存储多个邮箱（逗号、空格或换行分隔），现在需要将邮箱拆分到独立表，并通过映射表关联原表与邮箱表，面临两个核心问题：

1. 优化逗号分隔值的拆分与空元素过滤 #

你的现有方案可以简化，更高效的方式是结合regexp_split_to_table直接拆分并过滤空值，避免生成空元素后再处理：

SELECT 
  id AS cust_id,
  email
FROM test_customers,
     regexp_split_to_table(regexp_replace(TRIM(emails), '[\s,]+', ',', 'g'), ',') AS email
WHERE email <> '';

逻辑说明：

• TRIM(emails)先去除字段首尾的空白字符
• regexp_replace(..., '[\s,]+', ',', 'g')把所有连续的空白、逗号替换为单个逗号，统一分隔符
• regexp_split_to_table(..., ',')将处理后的字符串拆分为多行
• WHERE email <> ''过滤拆分后产生的空字符串（比如原字段首尾有分隔符的情况）

也可以用unnest+string_to_array的写法，效果一致：

SELECT 
  id AS cust_id,
  unnest(string_to_array(regexp_replace(TRIM(emails), '[\s,]+', ',', 'g'), ',')) AS email
FROM test_customers
WHERE unnest(string_to_array(regexp_replace(TRIM(emails), '[\s,]+', ',', 'g'), ',')) <> '';

2. 插入邮箱时保留原表ID并创建映射关系 #

你之前的报错是因为向test_emails插入时同时传递了cust_id，但该表只有email字段，导致字段不匹配。正确的做法是先拆分出原表ID和邮箱，再通过CTE分步插入：

完整执行代码

-- 1. 先拆分原表数据，过滤空邮箱
WITH split_emails AS (
  SELECT 
    id AS cust_id,
    regexp_split_to_table(regexp_replace(TRIM(emails), '[\s,]+', ',', 'g'), ',') AS email
  FROM test_customers
  WHERE email <> ''
),
-- 2. 插入邮箱到独立表，处理重复邮箱
inserted_emails AS (
  INSERT INTO test_emails (email)
  SELECT DISTINCT email FROM split_emails
  ON CONFLICT (email) DO NOTHING  -- 若邮箱已存在则跳过，符合UNIQUE约束
  RETURNING id AS email_id, email
)
-- 3. 关联原表ID与新邮箱ID，插入映射表
INSERT INTO test_map (cust_id, email_id)
SELECT se.cust_id, ie.email_id
FROM split_emails se
JOIN inserted_emails ie ON se.email = ie.email;

逻辑说明：

• split_emails：拆分原表数据，得到每个原表行ID对应的单个邮箱，过滤空值
• inserted_emails：将去重后的邮箱插入test_emails，利用ON CONFLICT避免违反唯一约束，同时返回新生成的email_id和邮箱地址
• 最后通过邮箱地址关联两个CTE，将原表ID和邮箱ID插入映射表

最后移除原表的emails字段

ALTER TABLE test_customers DROP COLUMN emails;

内容的提问来源于stack exchange，提问作者Thomas Tempelmann