sql - PostgresQL 错误 : operator does not exist: integer = integer[]

Question

我正在执行 SQL JOIN...ON，其中要连接的另一个表的列是一组行的数组，因此遇到此错误。具体来说，我正在对表执行 JOIN。

表:位置

+------------+------------+---------+----------+
| session_id |  gpstime   |   lat   | lon      |
+------------+------------+---------+----------+
|         49 | 1458203595 | 39.7449 | -8.8052  |
|         59 | 1458203601 | 39.7438 | -8.8057  |
|         95 | 1458203602 | 39.7438 | -8.8056  |
|         49 | 1458203602 | 39.7438 | -8.8057  |
+------------+------------+---------+----------+

表:行程

+-------------+-----------+---------+-----------+---------+-------------+
| session_ids | lat_start | lat_end | lon_start | lon_end | travel_mode |
+-------------+-----------+---------+-----------+---------+-------------+
| {49}        | 39.7449   | 41.1782 | -8.8053   | -8.5946 | car         |
| {59,60}     | 41.1551   | 41.1542 | -8.6294   | -8.6247 | foot        |
| {94,95}     | 41.1545   | 40.7636 | -8.6273   | -8.1729 | bike        |
+-------------+-----------+---------+-----------+---------+-------------+

这是我使用的查询:

SELECT gpstime, lat, lon,  travel_mode
FROM location
INNER JOIN trips
    ON session_id = session_ids
WHERE (lat BETWEEN SYMMETRIC lat_start AND lat_end) 
   AND (lon BETWEEN SYMMETRIC lon_start AND lon_end);

错误:

ERROR:  operator does not exist: integer = integer[]
LINE 4:  ON session_id = session_ids

我该如何解决这个问题？

Answer 1

= 比较器只能比较两个相同类型的值。但是在这里您试图将整数值与数组进行比较。所以值 1 不能等于看起来像 [1,2] 的值。

您可以使用 = ANY(...) 比较器来检查左值是否是右数组的一部分:

demo:db<>fiddle

ON session_id = ANY(session_ids)