PostgreSQL中基于IP关联表1与表2生成新表3的实现方法

这问题我熟，要实现用表1的IP匹配表2的NETWORK前缀，生成包含表1全量数据和对应SITE的表3，核心就是用关联查询+前缀匹配，下面给你两种可行的方案：

方案1：字符串前缀匹配（适合当前示例场景） #

如果你的NETWORK格式固定是xxx.xxx.xxx.这种三段加后缀点的形式，直接用字符串LIKE匹配最直接，代码如下：

-- 创建表3并插入数据
CREATE TABLE table3 AS
SELECT 
    t1.HOST,
    t1.IP,
    t1.SEVERITY,
    t2.SITE
FROM table1 t1
LEFT JOIN table2 t2 
    ON t1.IP LIKE CONCAT(t2.NETWORK, '%');

关键说明： #

• LEFT JOIN：确保表1的所有数据都会保留，哪怕某个IP找不到对应的NETWORK，SITE字段会显示NULL
• CONCAT(t2.NETWORK, '%')：把表2的NETWORK（比如172.2.36.）拼接成172.2.36.%，用来匹配所有以该前缀开头的IP地址

方案2：用PostgreSQL网络类型匹配（更健壮） #

如果后续可能出现复杂的子网情况（比如NETWORK不是三段前缀，或者担心字符串匹配的误判，比如172.2.3.匹配到172.2.30.1），推荐用PostgreSQL原生的inet和cidr类型来做精确的子网匹配：

CREATE TABLE table3 AS
SELECT 
    t1.HOST,
    t1.IP,
    t1.SEVERITY,
    t2.SITE
FROM table1 t1
LEFT JOIN table2 t2 
    ON t1.IP::inet <<= CONCAT(t2.NETWORK, '0/24')::cidr;

关键说明： #

• t1.IP::inet：把文本类型的IP转换为PostgreSQL的inet网络类型
• CONCAT(t2.NETWORK, '0/24')::cidr：把表2的NETWORK拼接成标准的CIDR子网格式（比如172.2.36.变成172.2.36.0/24），再转换为cidr类型
• <<=：PostgreSQL的网络操作符，用来判断左边的IP是否属于右边的子网，匹配更精准

补充：如果表3已经存在 #

如果表3的结构已经创建好了，只需要插入数据，把CREATE TABLE AS换成INSERT INTO即可：

INSERT INTO table3 (HOST, IP, SEVERITY, SITE)
SELECT 
    t1.HOST,
    t1.IP,
    t1.SEVERITY,
    t2.SITE
FROM table1 t1
LEFT JOIN table2 t2 
    -- 这里选择上面任意一种JOIN条件
    ON t1.IP LIKE CONCAT(t2.NETWORK, '%');

用以上任意一种方案，都能生成你预期的表3结构和数据~

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