1

I am doing an SQL JOIN...ON in which the column of the other table to join on is an array to a set of rows, and therefore encounter this error. Specifically, I'm doing the JOIN on the tables.

TABLE: location

+------------+------------+---------+----------+
| 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  |
+------------+------------+---------+----------+

TABLE: trips

+-------------+-----------+---------+-----------+---------+-------------+
| 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        |
+-------------+-----------+---------+-----------+---------+-------------+

Here's the query I used:

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:

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

How do I fix the issue?

arilwan
  • 3,374
  • 5
  • 26
  • 62

2 Answers2

2

The = comparator can only compare two values of the same type. But here you are trying to compare an integer value with an array. So the value 1 cannot equal a value that look like [1,2].

You can use the = ANY(...) comparator which checks if the left value is part of the right array:

demo:db<>fiddle

ON session_id = ANY(session_ids)
S-Man
  • 22,521
  • 7
  • 40
  • 63
0

S-Man is correct, although you can also use the ANY function as described here.

For more information on the differences between using IN and ANY/ALL, read this question.

Cargo23
  • 3,064
  • 16
  • 25