如何高效规范化PostgreSQL中的现有扁平表数据？

问题原因 #

你之前的CTE方案出现m×n条person记录，核心原因是插入地址的CTE仅返回生成的ID，没有保留与原source_data记录的关联关系，后续交叉连接时会把所有原数据行和所有新地址ID进行全匹配，导致冗余数据。

方案1：基于地址唯一标识关联（推荐，符合数据库规范化） #

这种方案先将扁平数据拆分为单人单地址的结构，然后去重插入地址表，最后通过地址的street和city字段关联到对应的person记录，自动处理重复地址的情况（同一个地址只插入一次，多人共享）。

WITH split_persons AS (
  -- 把source_data拆分为每条对应一个人的记录，包含姓名和地址信息
  SELECT
    person_1_name AS person_name,
    person_1_street AS addr_street,
    person_1_city AS addr_city
  FROM source_data
  UNION ALL
  SELECT
    person_2_name AS person_name,
    person_2_street AS addr_street,
    person_2_city AS addr_city
  FROM source_data
),
insert_addresses AS (
  -- 插入去重后的地址，返回新生成的地址ID和地址信息
  INSERT INTO address (street, city)
  SELECT DISTINCT addr_street, addr_city
  FROM split_persons
  RETURNING id, street, city
)
-- 关联地址信息插入person表，确保每个人对应正确的地址ID
INSERT INTO person (name, address_id)
SELECT sp.person_name, ia.id
FROM split_persons sp
JOIN insert_addresses ia 
  ON sp.addr_street = ia.street 
  AND sp.addr_city = ia.city;

方案2：基于行号顺序关联（适合需保留重复地址的场景） #

如果你的业务场景允许重复地址（即同一地址需多次插入地址表），可以通过行号来保证插入的地址顺序和拆分的人员顺序完全对应，避免依赖地址字段的唯一性。

WITH split_persons AS (
  -- 拆分数据并给每条记录分配行号，保证顺序
  SELECT
    person_name,
    addr_street,
    addr_city,
    ROW_NUMBER() OVER (ORDER BY source_id, person_type) AS rn
  FROM (
    SELECT
      id AS source_id,
      'person1' AS person_type,
      person_1_name AS person_name,
      person_1_street AS addr_street,
      person_1_city AS addr_city
    FROM source_data
    UNION ALL
    SELECT
      id AS source_id,
      'person2' AS person_type,
      person_2_name AS person_name,
      person_2_street AS addr_street,
      person_2_city AS addr_city
    FROM source_data
  ) AS t
),
insert_addresses AS (
  -- 按行号顺序插入地址，同时给插入的地址分配行号
  INSERT INTO address (street, city)
  SELECT addr_street, addr_city
  FROM split_persons
  ORDER BY rn
  RETURNING id, ROW_NUMBER() OVER () AS rn
)
-- 通过行号关联，确保每个人对应同顺序的地址ID
INSERT INTO person (name, address_id)
SELECT sp.person_name, ia.id
FROM split_persons sp
JOIN insert_addresses ia ON sp.rn = ia.rn;

说明 #

• 方案1更符合数据库规范化设计，减少地址表冗余数据，适合大多数场景。
• 方案2适用于必须保留重复地址记录的特殊业务需求，通过明确的排序和行号保证关联的准确性。

内容的提问来源于stack exchange，提问作者micha.net