Re: PostGIS 3.6.1 - ERROR: operator is not unique: public.geometry = public.geometry
Cedric Duprez <[email protected]> Thu, 8 Jan 2026 18:30:24 +0100
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
--------------YfqhkRMYoetfV8B56A7fwSf7 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Well done! You're right, I changed my search path excluding "public" from it before playing the query. If I add "public" in the search path, it works fine again. Cedric Le 08/01/2026 à 18:14, Regina Obe a écrit : > > Cedric, > > Good news. I was able to replicate. > > I noticed in your output you have the fully qualified type name, which > means you don’t have postgis schema in your search path. > > To replicate I did: > > set search_path='pg_catalog'; > > SELECT 'POINT EMPTY'::public.geometry = 'POINT EMPTY'::public.geometry; > > Gives: > > ERROR: operator is not unique: public.geometry = public.geometry LINE > 1: SELECT 'POINT EMPTY'::public.geometry = 'POINT EMPTY'::publi... ^ > > So I’m guessing we have some code in our = path that is not schema > qualified. > > I’ve ticketed the issue here: > > https://trac.osgeo.org/postgis/ticket/6033 > > Paul, > > Can you confirm you can replicate by changing your search_path? > > *From:*Cedric Duprez <[email protected]> > *Sent:* Thursday, January 8, 2026 12:05 PM > *To:* Regina Obe <[email protected]>; 'Paul Ramsey' <[email protected]> > *Cc:* [email protected] > *Subject:* Re: PostGIS 3.6.1 - ERROR: operator is not unique: > public.geometry = public.geometry > > I get two lines with the following query: > > SELECT oprcode, oprleft::regtype, oprright::regtype > FROM pg_catalog.pg_operator > WHERE oprname = '=' AND oprleft::regtype::text IN('public.geometry', > 'public.geography') > ORDER BY oprleft, oprright; > > Query result: > > oprcode |oprleft |oprright > -------------------+----------------+---------------- > public.geometry_eq |public.geometry |public.geometry > public.geography_eq|public.geography|public.geography > > Extensions are : > > extname |extversion > ------------------+---------- > plpgsql |1.0 > ogr_fdw |1.1 > plr |8.4.8.2 > pg_stat_statements|1.11 > pgcrypto |1.3 > tablefunc |1.0 > postgis_raster |3.6.1 > postgis |3.6.1 > > Le 08/01/2026 à 17:58, Regina Obe a écrit : > > I can’t replicate on my PostgreSQL 17.7 either even with upgrading > from PostGIS 3.5.3 to 3.6.1 > > PostgreSQL 17.7 on x86_64-windows, compiled by msvc-19.44.35221, > 64-bit POSTGIS="3.6.1 3.6.1" [EXTENSION] PGSQL="170" > GEOS="3.14.1-CAPI-1.20.5" PROJ="8.2.1 NETWORK_ENABLED=OFF > URL_ENDPOINT=https://cdn.proj.org > USER_WRITABLE_DIRECTORY=C:\Windows\ServiceProfiles\NetworkService\AppData\Local/proj > <file:///C:/Windows/ServiceProfiles/NetworkService/AppData/Local/proj> > DATABASE_PATH=C:\Program <file:///C:/Program> > Files\PostgreSQL\18\share\contrib\postgis-3.6\proj\proj.db" > (compiled against PROJ 8.2.1) LIBXML="2.12.5" LIBJSON="0.12" > LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" > > Perhaps you are having a conflict with another extension or it’s > issue with upgrade from earlier. I recall we did have to fix an > issue with = > > Can you show what the below queries output: > > SELECT oprcode, oprleft::regtype, oprright::regtype > > FROM pg_catalog.pg_operator > > WHERE oprname = '=' AND oprleft::regtype::text IN('geometry', > 'geography') > > ORDER BY oprleft, oprright; > > Should be: > > oprcode | oprleft | oprright > > --------------+-----------+----------- > > geometry_eq | geometry | geometry > > geography_eq | geography | geography > > (2 rows) > > SELECT extname, extversion FROM pg_catalog.pg_extension; > > *From:*Paul Ramsey via postgis-users > <[email protected]> > <mailto:[email protected]> > *Sent:* Thursday, January 8, 2026 11:26 AM > *To:* Cedric Duprez <[email protected]> > <mailto:[email protected]> > *Cc:* [email protected] > *Subject:* Re: PostGIS 3.6.1 - ERROR: operator is not unique: > public.geometry = public.geometry > > This is odd, I am not able to replicate... > > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > POSTGIS="3.6.1dev 3.6.0-6-gdb18a9a49" PGSQL="180" > GEOS="3.15.0dev-CAPI-1.21.0" PROJ="9.3.0 NETWORK_ENABLED=ON > URL_ENDPOINT=https://cdn.proj.org > USER_WRITABLE_DIRECTORY=/Users/pramsey/Library/Application > Support/proj DATABASE_PATH=/usr/local/share/proj/proj.db" > (compiled against PROJ 9.3.0) LIBXML="2.9.13" LIBJSON="0.17" > LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)" > (1 row) > > postgis_reg=# SELECT 'POINT EMPTY'::public.geometry = 'POINT > EMPTY'::public.geometry; > ?column? > ---------- > t > (1 row) > > Is this a database that has gone through upgrade stages, or a > blank fresh database? > > P > > On Thu, Jan 8, 2026 at 8:21 AM Cedric Duprez > <[email protected]> wrote: > > Hi all, > > I'm facing a potential bug with PostGIS 3.6.1 on PostgreSQL 17.7. > Here is what I get with postgis_full_version() : > POSTGIS="3.6.1 f533623" [EXTENSION] PGSQL="170" > GEOS="3.12.1-CAPI-1.18.1" PROJ="9.4.0 NETWORK_ENABLED=OFF > URL_ENDPOINT=https://cdn.proj.org > USER_WRITABLE_DIRECTORY=/tmp/proj > DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ > 9.4.0) GDAL="GDAL 3.8.4, released 2024/02/08" LIBXML="2.9.14" > LIBJSON="0.17" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)" RASTER > > When I execute this query: > SELECT 'POINT EMPTY'::public.geometry = 'POINT > EMPTY'::public.geometry; > I get the following error: > ERROR: operator is not unique: public.geometry = public.geometry > > It seems to be a regression, since I didn't have this error on > previous versions of PostGIS (3.5). > > How can this problem be solved? > Thanks in advance for you help, > > Cedric > --------------YfqhkRMYoetfV8B56A7fwSf7 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit <!DOCTYPE html><html><head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> </head> <body> <font face="Helvetica, Arial, sans-serif">Well done!<br> <br> You're right, I changed my search path excluding "public" from it before playing the query.<br> If I add "public" in the search path, it works fine again.<br> <br> Cedric<br> </font><br> <div class="moz-cite-prefix">Le 08/01/2026 à 18:14, Regina Obe a écrit :<br> </div> <blockquote type="cite" cite="mid:[email protected]"> <meta name="Generator" content="Microsoft Word 15 (filtered medium)"> <style>@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}@font-face {font-family:Aptos;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:12.0pt; font-family:"Aptos",sans-serif;}a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;}span.EmailStyle19 {mso-style-type:personal-reply; font-family:"Aptos",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt; mso-ligatures:none;}div.WordSection1 {page:WordSection1;}</style> <div class="WordSection1"> <p class="MsoNormal">Cedric,<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Good news. I was able to replicate.<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">I noticed in your output you have the fully qualified type name, which means you don’t have postgis schema in your search path.<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">To replicate I did:<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">set search_path='pg_catalog';<o:p></o:p></p> <p class="MsoNormal">SELECT 'POINT EMPTY'::public.geometry = 'POINT EMPTY'::public.geometry;<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Gives:<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">ERROR: operator is not unique: public.geometry = public.geometry LINE 1: SELECT 'POINT EMPTY'::public.geometry = 'POINT EMPTY'::publi... ^<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">So I’m guessing we have some code in our = path that is not schema qualified.<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">I’ve ticketed the issue here:<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><a href="https://trac.osgeo.org/postgis/ticket/6033" moz-do-not-send="true" class="moz-txt-link-freetext">https://trac.osgeo.org/postgis/ticket/6033</a><o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Paul,<o:p></o:p></p> <p class="MsoNormal">Can you confirm you can replicate by changing your search_path?<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <div style="border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt"> <div> <div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in"> <p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Cedric Duprez <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]"><[email protected]></a> <br> <b>Sent:</b> Thursday, January 8, 2026 12:05 PM<br> <b>To:</b> Regina Obe <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]"><[email protected]></a>; 'Paul Ramsey' <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]"><[email protected]></a><br> <b>Cc:</b> <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a><br> <b>Subject:</b> Re: PostGIS 3.6.1 - ERROR: operator is not unique: public.geometry = public.geometry<o:p></o:p></span></p> </div> </div> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal" style="margin-bottom:12.0pt">I get two lines with the following query:<br> <br> SELECT oprcode, <a href="oprleft::regtype" moz-do-not-send="true" class="moz-txt-link-freetext">oprleft::regtype</a>, <a href="oprright::regtype" moz-do-not-send="true" class="moz-txt-link-freetext">oprright::regtype</a><br> FROM pg_catalog.pg_operator<br> WHERE oprname = '=' AND <a href="oprleft::regtype::text" moz-do-not-send="true" class="moz-txt-link-freetext">oprleft::regtype::text</a> IN('public.geometry', 'public.geography')<br> ORDER BY oprleft, oprright;<br> <br> Query result:<br> <br> oprcode |oprleft |oprright <br> -------------------+----------------+----------------<br> public.geometry_eq |public.geometry |public.geometry <br> public.geography_eq|public.geography|public.geography<br> <br> Extensions are :<br> <br> extname |extversion<br> ------------------+----------<br> plpgsql |1.0 <br> ogr_fdw |1.1 <br> plr |8.4.8.2 <br> pg_stat_statements|1.11 <br> pgcrypto |1.3 <br> tablefunc |1.0 <br> postgis_raster |3.6.1 <br> postgis |3.6.1 <o:p></o:p></p> <div> <p class="MsoNormal">Le 08/01/2026 à 17:58, Regina Obe a écrit :<o:p></o:p></p> </div> <blockquote style="margin-top:5.0pt;margin-bottom:5.0pt"> <p class="MsoNormal">I can’t replicate on my PostgreSQL 17.7 either even with upgrading from PostGIS 3.5.3 to 3.6.1<o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal">PostgreSQL 17.7 on x86_64-windows, compiled by msvc-19.44.35221, 64-bit POSTGIS="3.6.1 3.6.1" [EXTENSION] PGSQL="170" GEOS="3.14.1-CAPI-1.20.5" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=<a href="https://cdn.proj.org" moz-do-not-send="true" class="moz-txt-link-freetext">https://cdn.proj.org</a> USER_WRITABLE_DIRECTORY=<a href="file:///C:/Windows/ServiceProfiles/NetworkService/AppData/Local/proj" moz-do-not-send="true">C:\Windows\ServiceProfiles\NetworkService\AppData\Local/proj</a> DATABASE_PATH=<a href="file:///C:/Program" moz-do-not-send="true">C:\Program</a> Files\PostgreSQL\18\share\contrib\postgis-3.6\proj\proj.db" (compiled against PROJ 8.2.1) LIBXML="2.12.5" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"<o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal">Perhaps you are having a conflict with another extension or it’s issue with upgrade from earlier. I recall we did have to fix an issue with = <o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal">Can you show what the below queries output:<o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal">SELECT oprcode, <a href="oprleft::regtype" moz-do-not-send="true" class="moz-txt-link-freetext">oprleft::regtype</a>, <a href="oprright::regtype" moz-do-not-send="true" class="moz-txt-link-freetext">oprright::regtype</a> <o:p></o:p></p> <p class="MsoNormal">FROM pg_catalog.pg_operator <o:p></o:p></p> <p class="MsoNormal">WHERE oprname = '=' AND <a href="oprleft::regtype::text" moz-do-not-send="true" class="moz-txt-link-freetext">oprleft::regtype::text</a> IN('geometry', 'geography')<o:p></o:p></p> <p class="MsoNormal">ORDER BY oprleft, oprright;<o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal">Should be:<o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal"> oprcode | oprleft | oprright<o:p></o:p></p> <p class="MsoNormal">--------------+-----------+-----------<o:p></o:p></p> <p class="MsoNormal">geometry_eq | geometry | geometry<o:p></o:p></p> <p class="MsoNormal">geography_eq | geography | geography<o:p></o:p></p> <p class="MsoNormal">(2 rows)<o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal">SELECT extname, extversion FROM pg_catalog.pg_extension;<o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <div style="border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt"> <div> <div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in"> <p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Paul Ramsey via postgis-users <a href="mailto:[email protected]" moz-do-not-send="true"><[email protected]></a> <br> <b>Sent:</b> Thursday, January 8, 2026 11:26 AM<br> <b>To:</b> Cedric Duprez <a href="mailto:[email protected]" moz-do-not-send="true"><[email protected]></a><br> <b>Cc:</b> <a href="mailto:[email protected]" moz-do-not-send="true" class="moz-txt-link-freetext">[email protected]</a><br> <b>Subject:</b> Re: PostGIS 3.6.1 - ERROR: operator is not unique: public.geometry = public.geometry</span><o:p></o:p></p> </div> </div> <p class="MsoNormal"> <o:p></o:p></p> <div> <p class="MsoNormal">This is odd, I am not able to replicate...<o:p></o:p></p> <div> <p class="MsoNormal"> <o:p></o:p></p> </div> <div> <p class="MsoNormal">--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> POSTGIS="3.6.1dev 3.6.0-6-gdb18a9a49" PGSQL="180" GEOS="3.15.0dev-CAPI-1.21.0" PROJ="9.3.0 NETWORK_ENABLED=ON URL_ENDPOINT=<a href="https://cdn.proj.org" moz-do-not-send="true" class="moz-txt-link-freetext">https://cdn.proj.org</a> USER_WRITABLE_DIRECTORY=/Users/pramsey/Library/Application Support/proj DATABASE_PATH=/usr/local/share/proj/proj.db" (compiled against PROJ 9.3.0) LIBXML="2.9.13" LIBJSON="0.17" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)"<br> (1 row)<br> <br> postgis_reg=# SELECT 'POINT EMPTY'::public.geometry = 'POINT EMPTY'::public.geometry;<br> ?column? <br> ----------<br> t<br> (1 row)<o:p></o:p></p> </div> <div> <p class="MsoNormal"> <o:p></o:p></p> </div> <div> <p class="MsoNormal"> <o:p></o:p></p> </div> <div> <p class="MsoNormal">Is this a database that has gone through upgrade stages, or a blank fresh database?<o:p></o:p></p> </div> <div> <p class="MsoNormal"> <o:p></o:p></p> </div> <div> <p class="MsoNormal">P<o:p></o:p></p> </div> </div> <p class="MsoNormal"> <o:p></o:p></p> <div> <div> <p class="MsoNormal">On Thu, Jan 8, 2026 at 8:21<span style="font-family:"Arial",sans-serif"> </span>AM Cedric Duprez <<a href="mailto:[email protected]" moz-do-not-send="true" class="moz-txt-link-freetext">[email protected]</a>> wrote:<o:p></o:p></p> </div> <blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt"> <div> <p class="MsoNormal">Hi all,<br> <br> I'm facing a potential bug with PostGIS 3.6.1 on PostgreSQL 17.7.<br> Here is what I get with postgis_full_version() : POSTGIS="3.6.1 f533623" [EXTENSION] PGSQL="170" GEOS="3.12.1-CAPI-1.18.1" PROJ="9.4.0 NETWORK_ENABLED=OFF URL_ENDPOINT=<a href="https://cdn.proj.org" target="_blank" moz-do-not-send="true" class="moz-txt-link-freetext">https://cdn.proj.org</a> USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.4.0) GDAL="GDAL 3.8.4, released 2024/02/08" LIBXML="2.9.14" LIBJSON="0.17" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)" RASTER<br> <br> When I execute this query: <br> SELECT 'POINT EMPTY'::public.geometry = 'POINT EMPTY'::public.geometry;<br> I get the following error:<br> ERROR: operator is not unique: public.geometry = public.geometry<br> <br> It seems to be a regression, since I didn't have this error on previous versions of PostGIS (3.5).<br> <br> How can this problem be solved?<br> Thanks in advance for you help,<br> <br> Cedric <o:p></o:p></p> </div> </blockquote> </div> </div> </blockquote> <p class="MsoNormal"><o:p> </o:p></p> </div> </div> </blockquote> <br> <div id="grammalecte_menu_main_button_shadow_host" style="width: 0px; height: 0px;"></div> </body> </html> --------------YfqhkRMYoetfV8B56A7fwSf7--