0

I am trying to dump a set of point/multipoint rows with this query:

   SELECT a.gid, a.nid, a.uid, a.cod_reg, postgis.st_dump(geom)
   FROM table a

but I get this error

ERROR: relation "geometry_dump" does not exist

PostgreSQL 9.6 running on Debian/Linux and Postgis 2.3

Same query works on another server with PostgreSQL 9.2 running on Debian/Linux and Postgis 2.0

Any hint?

2

It seems you have installed the PostGIS extension in the postgis schema in your DB (default is public).

Since you get an error for a sub-function, it means that the PostGIS extension looks at the wrong place to find all required functions. To solve this, make sure the user running this command has the postgis schema in its search path. You can read this answer for more details

SET search_path TO "$user", public, postgis;
|improve this answer|||||
  • I've already set search_path to postgis schema, no success (with new login) – rmorelli74 May 14 '18 at 13:30
  • According to postgis.net/docs/geometry_dump.html geometry_dump is a datatype that's what I miss... (still don't know why) – rmorelli74 May 14 '18 at 15:20
  • @rmorelli74 it is the data type returned by st_dump. If it can't be found, it will fail (and if there is no postgis specific returned data type, it would fail at the first postgis function) – JGH May 15 '18 at 1:32
  • after digging into privileges and moving back postgis extension to public schema I've found that "public" schema was not in my search path as regular user (thought with superuser privileges)... my fault. Thank you – rmorelli74 May 15 '18 at 5:37
1

Did you also installed in both cases postgis extension in your database with CREATE EXTENSION postgis;?

You may look at the doc as no postgis.st_dump exists.

You should do instead

SELECT a.gid, a.nid, a.uid, a.cod_reg, st_dump(geom)
   FROM table a

to solve your issue

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.