The && operator for postgis types is defined in the schema you have postgis installed in. In this case public.
operators for built-in types are stored in the pg_catalog schema. pg_catalog is always in the search path even if you don't explicitly add it. So this should work though it's horribly ugly. It's what we do within postgis code. SELECT 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry OPERATOR(public.&&) 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry; As to why your below example works, I suspect it's our geometry auto cast to postgresql box/point/polygon thus using the pg_catalog built in && designed for postgres built-in geometric types. We have a ticket in place for this as it has another ugly side-effect with != https://trac.osgeo.org/postgis/ticket/5175 select public.ST_3DMakeBox(public.ST_MakePoint(0,0,0), public.ST_MakePoint(2,2,2)) && public.ST_3DMakeBox(public.ST_MakePoint(0,0,0), public.ST_MakePoint(2,2,2)); > -----Original Message----- > From: postgis-users [mailto:[email protected]] On Behalf > Of Chris Mair > Sent: Wednesday, October 5, 2022 3:27 PM > To: [email protected] > Subject: [postgis-users] operators and search paths > > Hi, > > here is something strange I found about operators and search paths. > > > -> Background. > > I'm on AWS/RDS/PostgreSQL, PostgreSQL is "PostgreSQL 14.4 on x86_64-pc- > linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit". > PostGIS is "3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1". PostGIS has been > activated simply by "create extension postgis" from the version installed by > RDS. > > > -> The problem. > > If I run: > > postgres=> select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry && > 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry; > ?column? > ---------- > t > (1 row) > > all is fine. But, if I run the same query in a situation where I altered my search > path: > > postgres=> set search_path = chris; > SET > > Postgres doesn't find the operator: > > postgres=> select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry && > 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry; > ERROR: operator does not exist: public.geometry && public.geometry > LINE 1: ...YGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry && 'POLYGO... > ^ > HINT: No operator matches the given name and argument types. You might > need to add explicit type casts. > > However, other versions of && seem to run just fine (not completely sure > which one this is, I think the gidx && gidx one): > > postgres=> show search_path; > search_path > ------------- > chris > (1 row) > > postgres=> select public.ST_3DMakeBox(public.ST_MakePoint(0,0,0), > public.ST_MakePoint(2,2,2)) && > public.ST_3DMakeBox(public.ST_MakePoint(0,0,0), > public.ST_MakePoint(2,2,2)); > ?column? > ---------- > t > (1 row) > > > -> The expectation. > > Either nothing works or everything works. I was not expecting the inconsistent > behaviour of one operator needing "public" in the search path and the other > one not needing it. > > > -> Some investigation. > > It looks like PostGIS stores its function in the schema public. Fine. > However, I see operators are mapped to functions sometimes with the explicit > schema "public" and sometimes without: > > postgres=> select > (select typname from pg_type where oid = oprleft) as left_type, > oprname, > (select typname from pg_type where oid = oprright) as right_type, > oprcode > from pg_operator > where oprname = '&&' and oprcode::text like '%geo%'; > > left_type | oprname | right_type | oprcode > -----------+---------+------------+---------------------- > geometry | && | geometry | geometry_overlaps > geography | && | geography | geography_overlaps > geography | && | gidx | public.overlaps_geog > gidx | && | geography | public.overlaps_geog > gidx | && | gidx | public.overlaps_geog > (5 rows) > > Wouldn't it make sense for PostGIS to hardwire the path to where the > functions are installed for *all* of the operators? So all the operators are > available regardless search path? > > Or is something completely different happening here (then please explain :)? > > Thanks & Bye, > Chris. > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
