3

Context

I have to set up a postgresql database with several schemas.

To separate things, I decided to install postgis elsewhere than the public schema, based on https://postgis.net/2017/11/07/tip-move-postgis-schema/ and Using schema other than public in PostGIS? for example.

I also installed other extensions, such as pgrouting or fuzzystrmatch or citext for example, which by default went into the main of my project schemas called main. And also postgis_topology which is set up into its own schema called "topology", if I'm not wrong.

For my own culture ; is it a good practice to centralise all extensions in one unique schema?

Error

Now, I want to use postgis functions in all my project schemas, but unfortunately, the postgis functions don't seem to be available as I went into this error while creating a database:

psql:create_db.sql:104: ERROR:  type "geometry" does not exist

What's really strange for me is that, OK, the script stops on this error, but when I manually create the table that precisely raises this error, for example with pgAdmin, it goes well!

Question

So why, during the execution of the script, it somehow doesn't find postgis when creating a table with a geometry column, but when creating the table in pgAdmin it does?

It sounds like an async process that is not finished...

Information

"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

"2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

  • Are you using the same user in pgAdmin and to load your script? That sounds a lot like the postgis schema is not in the user search path – JGH Mar 19 at 19:08
  • is it a good practice to centralise all extensions in one unique schema? Yes! (not so much for the centralisation than for being in a different place than the data) And this schema is commonly named public. – JGH Mar 19 at 19:10
  • I have this line before all the table creation statements; ALTER DATABASE my_database SET search_path="$user", public, postgis, topology; because, effectively, all schemas (included the postgis schema) are own by another role than postgres. – s.k Mar 19 at 19:35
  • 1
    doing this will set the default search path for the next session, not the current one. Try with SET search_path TO "$user", public, postgis, topology; to set it immediatly – JGH Mar 19 at 19:42
  • Great! This works! – s.k Mar 19 at 19:47
4

The PostGIS extension must be in the user search_path. Setting the search_path using ALTER DATABASE or ALTER ROLE will have effect at the next session only. To use the new path immediately, one would rather use SET search_path TO "$user", public, postgis, topology;

|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.