postgresql - SQL 查询选择公共(public) IP 地址

Question

我在 postgresql 8.3 数据库上。我试图找出下面查询中的错误所在。我正在尝试设计一个查询以仅选择作为私有(private)地址的 source_ips 和 destination_ips。

出于某种原因，在下面的查询中获取的地址之一是地址 208.117.252.39，它不是私有(private)地址。

Is there something wrong with the logic in the query below that would make it select public IP addresses as well?

 select source_ip, destination_ip
from ip_table
where
    (
    inet '10/8' >> source_ip
    or inet '192.168/16' >> source_ip
    or source_ip >= inet '172.16/16' and source_ip < inet '172.32/16'
     )
    and  inet '10/8' >> destination_ip
    or  inet '192.168/16' >> destination_ip

Answer 1

由于 and 操作的优先级高于 or ，因此您缺少括号。您的查询等同于:

select source_ip, destination_ip
from ip_table
where
    (
        (
        inet '10/8' >> source_ip
        or inet '192.168/16' >> source_ip
        or source_ip >= inet '172.16/16' and source_ip < inet '172.32/16'
        )
        and  inet '10/8' >> destination_ip
    )
    or  inet '192.168/16' >> destination_ip

正确的版本:

select source_ip, destination_ip
from ip_table
where
    (
        inet '10/8' >> source_ip
        or inet '192.168/16' >> source_ip
        or source_ip >= inet '172.16/16' and source_ip < inet '172.32/16'
    )
    and
    (
        inet '10/8' >> destination_ip
        or  inet '192.168/16' >> destination_ip
    )