arrays - 加入 inet[] 到 inet

Question

我正在尝试连接两个表:

peering_autonomoussystem.potential_internet_exchange_peering_sessions(包含 ipv4 或 ipv6 地址数组)

和

peeringdb_networkixlan.ipaddr4(包含一个简单的 ipv4 地址)

和

peeringdb_networkixlan.ipaddr6(包含一个简单的 ipv6 地址)

我在弄清楚如何将 inet 值与未嵌套的 inet[] 数组的值相关联以完成连接时遇到问题。

我尝试了不同的变体来尝试使某些东西起作用，但我没有取得太大进展。我想知道是否有人可以按我的方式提出一些建议？

select
 peering_autonomoussystem.asn,
 peering_autonomoussystem.name,
 unnest(peering_autonomoussystem.potential_internet_exchange_peering_sessions),
 peeringdb_networkixlan.ipaddr4,
 peeringdb_networkixlan.ipaddr6,
 peeringdb_networkixlan.name
from
 (
 select unnest(peering_autonomoussystem.potential_internet_exchange_peering_sessions)
     from peering_autonomoussystem
 ) as inet(ip)
join
 peeringdb_networkixlan
on
 peeringdb_networkixlan.ipaddr4 = inet.ip

ERROR:  missing FROM-clause entry for table "peering_autonomoussystem"
LINE 2:  peering_autonomoussystem.asn,
         ^
SQL state: 42P01
Character: 9

peering_manager=# select
peering_manager-#  peering_autonomoussystem.asn,
peering_manager-#  peering_autonomoussystem.name,
peering_manager-#  unnest(peering_autonomoussystem.potential_internet_exchange_peering_sessions),
peering_manager-#  peeringdb_networkixlan.ipaddr4,
peering_manager-#  peeringdb_networkixlan.ipaddr6,
peering_manager-#  peeringdb_networkixlan.name
peering_manager-# from
peering_manager-#  peering_autonomoussystem
peering_manager-# join
peering_manager-#  peeringdb_networkixlan
peering_manager-# on
peering_manager-#  peeringdb_networkixlan.ipaddr4 @> peering_autonomoussystem.potential_internet_exchange_peering_sessions
peering_manager-#
peering_manager-# ;
ERROR:  operator does not exist: inet @> inet[]
LINE 13:  peeringdb_networkixlan.ipaddr4 @> peering_autonomoussystem....
                                         ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
peering_manager=#

这是我正在使用的两个表中的数据集:

peering_manager=# \d peering_autonomoussystem
                                                        Table "public.peering_autonomoussystem"
                    Column                    |           Type           | Collation | Nullable |                       Default
----------------------------------------------+--------------------------+-----------+----------+------------------------------------------------------
 id                                           | integer                  |           | not null | 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=# select
 peering_autonomoussystem.asn,
 peering_autonomoussystem.name,
 unnest(peering_autonomoussystem.potential_internet_exchange_peering_sessions)
from
 peering_autonomoussystem limit 10;
  asn  |              name              |         unnest
-------+--------------------------------+-------------------------
  5400 | BT                             | 2001:504:1::a500:5400:1
  5400 | BT                             | 198.32.160.80
 21724 | Radiant Communications Toronto | 2001:504:1a::34:102
  8002 | Stealth Communications         | 2001:504:1::a500:8002:1
  8002 | Stealth Communications         | 2001:504:36::1f42:0:1
  8002 | Stealth Communications         | 206.82.104.194
 10835 | Visionary Communications, Inc. | 2001:504:16::2a53
 10835 | Visionary Communications, Inc. | 206.81.80.57
 23473 | Pavlov Media INC.              | 2001:504:17:115::224
 23473 | Pavlov Media INC.              | 206.126.115.224
(10 rows)

peering_manager=#

peering_manager=# \d peeringdb_networkixlan;
                                      Table "public.peeringdb_networkixlan"
   Column   |          Type          | Collation | Nullable |                      Default
------------+------------------------+-----------+----------+----------------------------------------------------
 id         | integer                |           | not null | nextval('peeringdb_networkixlan_id_seq'::regclass)
 asn        | bigint                 |           | not null |
 ipaddr6    | inet                   |           |          |
 ipaddr4    | inet                   |           |          |
 is_rs_peer | boolean                |           | not null |
 ix_id      | integer                |           | not null |
 name       | character varying(255) |           | not null |
 ixlan_id   | integer                |           | not null |
Indexes:
    "peeringdb_networkixlan_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "peeringdb_networkixlan_ix_id_check" CHECK (ix_id >= 0)
    "peeringdb_networkixlan_ixlan_id_check" CHECK (ixlan_id >= 0)
Referenced by:
    TABLE "peeringdb_peerrecord" CONSTRAINT "peeringdb_peerrecord_network_ixlan_id_97d3feaa_fk_peeringdb" FOREIGN KEY (network_ixlan_id) REFERENCES peeringdb_networkixlan(id) DEFERRABLE INITIALLY DEFERRED

peering_manager=# select
peering_manager-#  peeringdb_networkixlan.ipaddr4,
peering_manager-#  peeringdb_networkixlan.ipaddr6,
peering_manager-#  name
peering_manager-# from
peering_manager-#  peeringdb_networkixlan
peering_manager-# where
peering_manager-#  ipaddr6 = '2001:504:1::a500:5400:1'
peering_manager-# or
peering_manager-#  ipaddr4 = '198.32.160.80'
peering_manager-# or
peering_manager-#  ipaddr6 = '2001:504:1a::34:102'
peering_manager-# or
peering_manager-#  ipaddr6 = '2001:504:1::a500:8002:1'
peering_manager-# or
peering_manager-#  ipaddr4 = '206.82.104.194'
peering_manager-# or
peering_manager-#  ipaddr6 = '2001:504:16::2a53'
peering_manager-# or
peering_manager-#  ipaddr4 = '206.81.80.57'
peering_manager-# or
peering_manager-#  ipaddr6 = '2001:504:17:115::224'
peering_manager-# or
peering_manager-#  ipaddr4 = '206.126.115.224';
     ipaddr4     |         ipaddr6         |                     name
-----------------+-------------------------+----------------------------------------------
 206.108.34.102  | 2001:504:1a::34:102     | TorIX
 206.126.115.224 | 2001:504:17:115::224    | Digital Realty New York
 198.32.160.80   | 2001:504:1::a500:5400:1 | NYIIX
 198.32.160.33   | 2001:504:1::a500:8002:1 | NYIIX
 206.81.80.57    | 2001:504:16::2a53       | SIX Seattle: MTU 1500
 206.82.104.194  | 2001:504:36::1f42:0:1   | DE-CIX New York: DE-CIX New York Peering LAN
(6 rows)

peering_manager=#

更新:

根据@Laurenz Albe 的建议，我改进了查询以使用 ANY 产生了积极的结果，尽管查询需要改进。以pas.asn = 812为例:

select
 pas.asn asnumber,
 pas.name asname,
 pas.potential_internet_exchange_peering_sessions potential,
 pdbnil.ipaddr4 ipv4,
 pdbnil.ipaddr6 ipv6,
 pdbnil.name ixname
from
 peering_autonomoussystem pas
join
 peeringdb_networkixlan pdbnil
on
 pdbnil.ipaddr4 = any(pas.potential_internet_exchange_peering_sessions)
or
 pdbnil.ipaddr6 = any(pas.potential_internet_exchange_peering_sessions)
where pas.asn = 812;
 asnumber |    asname    |                            potential                             |     ipv4      |        ipv6        |     ixname
----------+--------------+------------------------------------------------------------------+---------------+--------------------+-----------------
      812 | Rogers Cable | {2001:504:12::14,198.32.134.32,2001:504:1a::34:29,206.108.34.29} | 198.32.134.32 | 2001:504:12::14    | Equinix Seattle
      812 | Rogers Cable | {2001:504:12::14,198.32.134.32,2001:504:1a::34:29,206.108.34.29} | 206.108.34.29 | 2001:504:1a::34:29 | TorIX
(2 rows)

我想要的上述结果是看到 4 行，每行对应 potential 数组中的每个值。我相信连接中的 OR 至少可以部分实现这一点，但似乎没有。

Answer 1

您的加入条件有两个选项:

1. ANY 运算符:

   peeringdb_networkixlan.ipaddr4
   = ANY (peering_autonomoussystem.potential_internet_exchange_peering_session)
   

   此版本可以在 peeringdb_networkixlan(ipaddr4) 上使用 b 树索引。

2. 数组“包含”运算符:

   ARRAY[peeringdb_networkixlan.ipaddr4]
   <@ peering_autonomoussystem.potential_internet_exchange_peering_session
   

   此版本可以在 peering_autonomoussystem(potential_internet_exchange_peering_session) 上使用 GIN 索引。

您可能想尝试两者，看看哪一个更适合您的情况。

一般来说，如果您想连接单个数组元素，最好不要使用数组。在这种情况下，通常最好对数据进行归一化。