PostgresQL ERROR: operator does not exist: integer = integer[]

Question

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?

S-Man · Accepted Answer · 2019-09-30 14:59:10Z

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)

edited Sep 30, 2019 at 14:59

answered Sep 30, 2019 at 14:42

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cargo23 · Accepted Answer · 2019-09-30 14:48:00Z

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.

answered Sep 30, 2019 at 14:48

Cargo23

3,18918 silver badges27 bronze badges

Collectives™ on Stack Overflow

PostgresQL ERROR: operator does not exist: integer = integer[]

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related