arrays - Postgres 8.4.3 : How do convert names and string of names to normalized numerical ids using unnest

Question

我有一个包含两个字符字段的表 region_town_names，一个包含区域名称，另一个包含以逗号分隔的城镇列表

region  | towns
-------------------------------
regionA | townA, townB, townC    
regionB | townB, townD

我还有两个表(region_id 和 town_id)，其中包含每个地区/城镇的数字 ID

id  | name                              id  |  name
---------------                         ----------------
 1  | regionA                            1  |  townA
 2  | regionB                            2  |  townB
                                         3  |  townC
                                         4  |  townD

我现在正在尝试填充一个标准化表 region_town_ids，它应该取消城镇列表的嵌套并包含区域和城镇的 ID，如下所示:

 region_id | town_id
 -------------------
    1      |    1
    1      |    2
    1      |    3
    2      |    2
    2      |    4

我可以轻松获取名称并将它们插入

 insert into region_town_ids
   select region as region_id, unnest(string_to_array(towns,', ')) as town_id 
   from region_town_names;

但是我如何在同一语句中查找名称的 ID 并插入它们而不是名称？那可能吗？我需要一个 psql 函数吗？谢谢。

Answer 1

SQL Fiddle

select
    rid.id region_id,
    tid.id town_id
from
    (
        select
            region region_name,
            unnest(string_to_array(towns,', ')) town_name
        from region_town_names
    ) rtn
    inner join
    region_id rid on rid.name = rtn.region_name 
    inner join
    town_id tid on tid.name = rtn.town_name

这回答了您的问题，但我怀疑您弄错了。请注意 town_id #2 如何属于两个区域。可能吗？

此外，我认为您可以简化模型，消除 region_town_ids 表并制作 town_id 表，如下所示:

(id, region_id, name)