选择列与inet[]阵列中的任何IP地址匹配的行

Question

我正在尝试显示在inet[]类型列中至少有一个值的行。

我真的不知道更好的方法了，所以它看起来是最简单的方法，但它返回的结果是{}，根据inet[]类型，我猜它是null，但从is not null查询的角度来看不是这样。

peering_manager=# select asn,name,potential_internet_exchange_peering_sessions from peering_autonomoussystem where potential_internet_exchange_peering_sessions is not null order by potential_internet_exchange_peering_sessions limit 1;
 asn  |              name               | potential_internet_exchange_peering_sessions
------+---------------------------------+----------------------------------------------
 6128 | Cablevision Systems Corporation | {}
(1 row)

peering_manager=#

因此，尝试更深入地挖掘它，我认为如果我可以尝试匹配inet[]列中任何有效IP地址的存在，这可能会起作用，但是我收到了一个错误，我不明白它指的是什么，也不知道如何解决它才能达到预期的结果：

peering_manager=# select asn,name,potential_internet_exchange_peering_sessions from peering_autonomoussystem where potential_internet_exchange_peering_sessions << inet '0.0.0.0/0';
ERROR:  operator does not exist: inet[] << inet
LINE 1: ...here potential_internet_exchange_peering_sessions << inet '0...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
peering_manager=#

可能是说<<运算符对于inet[]类型无效，或者在试图从存储为inet[]类型的值查询inet类型时，<<运算符是无效操作？还是别的什么？

不管怎样，我有点迷茫了。也许有更好的方法来做这件事？

这是表格，以及我正在使用的数据集的示例。

peering_manager=# \d peering_autonomoussystem;
                                                     Table "public.peering_autonomoussystem"
                    Column                    |           Type           |                               Modifiers
----------------------------------------------+--------------------------+-----------------------------------------------------------------------
 id                                           | integer                  | not null default nextval('peering_autonomoussystem_id_seq'::regclass)
 asn                                          | bigint                   | not null
 name                                         | character varying(128)   | not null
 comment                                      | text                     | not null
 ipv6_max_prefixes                            | integer                  | not null
 ipv4_max_prefixes                            | integer                  | not null
 updated                                      | timestamp with time zone |
 irr_as_set                                   | character varying(255)   |
 ipv4_max_prefixes_peeringdb_sync             | boolean                  | not null
 ipv6_max_prefixes_peeringdb_sync             | boolean                  | not null
 irr_as_set_peeringdb_sync                    | boolean                  | not null
 created                                      | timestamp with time zone |
 potential_internet_exchange_peering_sessions | inet[]                   | not null
 contact_email                                | character varying(254)   | not null
 contact_name                                 | character varying(50)    | not null
 contact_phone                                | character varying(20)    | not null
Indexes:
    "peering_autonomoussystem_pkey" PRIMARY KEY, btree (id)
    "peering_autonomoussystem_asn_ec0373c4_uniq" UNIQUE CONSTRAINT, btree (asn)
Check constraints:
    "peering_autonomoussystem_ipv4_max_prefixes_check" CHECK (ipv4_max_prefixes >= 0)
    "peering_autonomoussystem_ipv6_max_prefixes_check" CHECK (ipv6_max_prefixes >= 0)
Referenced by:
    TABLE "peering_directpeeringsession" CONSTRAINT "peering_directpeerin_autonomous_system_id_691dbc97_fk_peering_a" FOREIGN KEY (autonomous_system_id) REFERENCES peering_autonomoussystem(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "peering_internetexchangepeeringsession" CONSTRAINT "peering_peeringsessi_autonomous_system_id_9ffc404f_fk_peering_a" FOREIGN KEY (autonomous_system_id) REFERENCES peering_autonomoussystem(id) DEFERRABLE INITIALLY DEFERRED

peering_manager=#

peering_manager=# select asn,name,potential_internet_exchange_peering_sessions from peering_autonomoussystem limit 7;
  asn  |              name               |                                                                 potential_internet_exchange_peering_sessions
-------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 37662 | WIOCC                           | {2001:504:1::a503:7662:1,198.32.160.70}
 38001 | NewMedia Express Pte Ltd        | {2001:504:16::9471,206.81.81.204}
 46562 | Total Server Solutions          | {2001:504:1::a504:6562:1,198.32.160.12,2001:504:16::b5e2,206.81.81.81,2001:504:1a::35:21,206.108.35.21,2001:504:2d::18:80,198.179.18.80,2001:504:36::b5e2:0:1,206.82.104.156}
 55081 | 24Shells Inc                    | {2001:504:1::a505:5081:1,198.32.160.135}
 62887 | Whitesky Communications         | {2001:504:16::f5a7,206.81.81.209}
  2603 | NORDUnet                        | {2001:504:1::a500:2603:1,198.32.160.21}
  6128 | Cablevision Systems Corporation | {}
(7 rows)

Answer 1

您可以使用array_length()。在空数组或空值上，这将返回NULL。

...
WHERE array_length(potential_internet_exchange_peering_sessions, 1) IS NOT NULL
...

Answer 2

最好将数组长度与整数进行比较

...
WHERE array_length(potential_internet_exchange_peering_sessions, 1) > 0
...