如何在PostgreSQL中拆分城市、州、国家地址字符串并避免重复

问题根源 #

你之前用regexp_split_to_table导致数据重复，是因为这个函数会把每个拆分后的元素单独生成一行记录，原表的一行数据会被拆分成N行（N等于拆分后的部分数），自然就会产生重复。

基础解决方案（满足核心需求） #

如果只需要筛选出包含至少3个地理部分的记录，把第一部分作为城市，剩余部分合并为地区，可以用数组拆分函数来处理，不会额外生成行：

SELECT 
    location_name,
    -- 取拆分后的第一个元素作为城市
    regexp_split_to_array(location_name, E',\\s*')[1] AS city,
    -- 从第二个元素开始合并为地区
    array_to_string(regexp_split_to_array(location_name, E',\\s*')[2:], ', ') AS region
FROM public.users_geo
-- 筛选出至少有3个部分的记录（数组长度≥3）
WHERE array_length(regexp_split_to_array(location_name, E',\\s*'), 1) >= 3;

这里E',\\s*'是为了处理逗号后带空格的情况，确保拆分后的元素没有多余空格；array_to_string用来把数组的部分元素拼接成字符串。

特殊情况处理（比如带缩写的城市名） #

像Washington, D.C., United States这种，按逗号拆分后会得到3个部分，但实际Washington, D.C.才是城市。可以通过CASE语句判断第二个元素是否包含点号，合并前两个元素作为城市：

SELECT 
    location_name,
    CASE
        -- 如果第二个元素包含点号，合并前两个元素作为城市
        WHEN array_length(split_arr, 1) >=3 AND split_arr[2] ~ '\.'
            THEN split_arr[1] || ', ' || split_arr[2]
        ELSE split_arr[1]
    END AS city,
    CASE
        WHEN array_length(split_arr, 1) >=3 AND split_arr[2] ~ '\.'
            THEN array_to_string(split_arr[3:], ', ')
        ELSE array_to_string(split_arr[2:], ', ')
    END AS region
FROM (
    SELECT 
        location_name,
        regexp_split_to_array(location_name, E',\\s*') AS split_arr
    FROM public.users_geo
    WHERE array_length(regexp_split_to_array(location_name, E',\\s*'), 1) >=3
) AS sub;

这样处理后，带缩写的城市名就能正确识别，同时避免数据重复。

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