将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数组中的每个值对应一行。我相信join中的OR已经实现了这一点，至少在一定程度上是这样，但似乎并非如此。

Answer 1

您的联接条件有两个选项：

1. a operator with ANY：

peeringdb_networkixlan.ipaddr4 =任何(peering_autonomoussystem.potential_internet_exchange_peering_session)

此版本可以在peeringdb_networkixlan(ipaddr4).

• The数组“包含”运算符上使用b树索引：

peering_autonomoussystem.potential_internet_exchange_peering_session <@ ARRAYpeeringdb_networkixlan.ipaddr4

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

你可能想同时尝试两种方法，看看哪一种更适合你的情况。

通常，如果您想在单个数组元素上联接，最好不要使用数组。通常，在这种情况下，最好对数据进行规范化。