'[postgis-users] NOT IN and IN predicates are true at the same time'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    [postgis-users] NOT IN and IN predicates are true at the same time
From:       Caleb Land via postgis-users <postgis-users () lists ! osgeo ! org>
Date:       2023-10-25 14:31:49
Message-ID: 09894D33-6521-4C7C-AC87-0A2B7EBB1EDB () land ! fm
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

Hi,

I'm very new to PostGIS and I'm running into a seemingly strange situation.

I have a table of geography points, and I am trying to select rows where the point is \
NOT IN a given list:

  SELECT "address_coordinates".* FROM "address_coordinates" WHERE \
"address_coordinates"."address_id" = 2269 AND (lonlat NOT IN ('POINT(-100.768049 \
46.784703)'))

However, this returns rows where the the lonlat column IS the same point because I \
get the same result row when I run the query with the NOT removed:

  SELECT "address_coordinates".* FROM "address_coordinates" WHERE \
"address_coordinates"."address_id" = 2269 AND (lonlat IN ('POINT(-100.768049 \
46.784703)'))

I can get the expected results if I move the NOT to before the column name:

  SELECT "address_coordinates".* FROM "address_coordinates" WHERE \
"address_coordinates"."address_id" = 2269 AND NOT (lonlat IN ('POINT(-100.768049 \
46.784703)'))

Any idea what is causing this?

Caleb

[Attachment #5 (unknown)]

<html><head><meta http-equiv="content-type" content="text/html; \
charset=utf-8"></head><body style="overflow-wrap: break-word; -webkit-nbsp-mode: \
space; line-break: after-white-space;"><div>Hi,</div><div><br></div>I'm very new to \
PostGIS and I'm running into a seemingly strange situation.<div><br></div><div>I have \
a table of geography points, and I am trying to select rows where the point is <font \
face="Menlo">NOT IN</font> a given list:</div><div><br></div><div>&nbsp; <font \
face="Menlo">SELECT "address_coordinates".* FROM "address_coordinates" WHERE \
"address_coordinates"."address_id" = 2269 AND (lonlat NOT IN ('POINT(-100.768049 \
46.784703)'))</font></div><div><br></div><div>However, this returns rows where the \
the lonlat column IS the same point because I get the same result row when I run the \
query with the <font face="Menlo">NOT</font> removed:</div><div><br></div><div>&nbsp; \
<font face="Menlo">SELECT "address_coordinates".* FROM "address_coordinates" WHERE \
"address_coordinates"."address_id" = 2269 AND (lonlat IN ('POINT(-100.768049 \
46.784703)'))</font></div><div><br></div><div><br></div><div><br></div><div>I can get \
the expected results if I move the <font face="Menlo">NOT</font> to before the column \
name:</div><div><br></div><div>&nbsp; <font face="Menlo">SELECT \
"address_coordinates".* FROM "address_coordinates" WHERE \
"address_coordinates"."address_id" = 2269 AND NOT (lonlat IN ('POINT(-100.768049 \
46.784703)'))</font></div><div><br></div><div><br></div><div>Any idea what is causing \
this?</div><div><br></div><div>Caleb</div></body></html>

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 