Postgresql – PostGIS ERROR: operator does not exist: postgis.geometry @ geometry

postgispostgresql

I have had PostgreSQL 9.3.1 and PostGIS 2.1.0 installed (via https://github.com/PostgresApp/PostgresApp/releases/tag/9.3.1.1-RC1) and working on my laptop for some time, and have been doing Ruby (2.0.0) on Rails (4.0.0) development with them.

I was able to successfully run typical operations, for example using @ to determine whether a point (postgis.geometry(Point,3785)) is in a bounding box. Most recently I took the exact points and query I was successfully running in dev and made an equivalent feature test. This is when I began noticing issues…

Whereas a point on the dev side was stored like this (SRID 3785):

POINT (-13740247.269752283 6126532.67447704)

…the test side saw it like this:

POINT (-8238307.241152702 4970299.628630948)

I wanted to post a Stack Overflow question about the issue, so I wanted the exact DB version. Whereas select postgis_full_version(); previously worked (it's still in my Terminal scrollback history), now it was returning ERROR: function postgis_full_version() does not exist. At that point I didn't think to check the search_path, and instead I followed https://stackoverflow.com/a/8459682 — first I tried CREATE EXTENSION postgis; but got ERROR: extension "postgis" already exists — and then I executed:

psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/postgis.sql
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/spatial_ref_sys.sql
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/postgis_comments.sql

Unfortunately those did not do the trick, and worse, now when I run my usual query I am seeing:

PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry

How could things go so wrong? I have not yet reset my computer. I only tried restarting PostgreSQL so far. Any ideas? Thank you for reading.

Best Answer

Sounds to me like you have PostGIS installed in your database more than once, possibly once via CREATE EXTENSION and again via a load script, or from a dump.

The hint is that:

PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry

mentions the schema qualifier postgis.geometry for one operand, and omits it for another. I'd say you have two copies of the the geometry type in different schemas, probably public and postgis.

Related Question