1

I have a postgres database with the postgis extension enabled in the public schema. We have put the public schema in the search path, but still can only get the postgis function (i.e. st_x() ) if we explicitly refer to the public schema, but we get:

select st_x(obspoint_geom) from sc2.observation_points; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ********** Error **********

ERROR: function st_x(public.geometry) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 8

Any idea what is happening?

  • 1
    assuming you set up postgis extension, try select st_x(st_makepoint(0.0,1.0)). If that works, the problem might be the type of your obspoint_geom column – Steven Kay Apr 4 '18 at 18:38
2

Did you create the PostGIS extension in your database? You can add PostGIS to an existing database using the following SQL command in psql or pgadmin

CREATE EXTENSION postgis;

In case of a new database you can use a template with PostGIS enabled

CREATE DATABASE your_database_name TEMPLATE=template_postgis;

In a PostGIS enabled database you should see a table "spatial_ref_sys" and two views "geography_columns" and "geometry_columns". If you get an error message entering "CREATE" command above then postgis is not installed properly on your machine.

  • as a point of clarification - if I run select public.st_x(obspoint_geom) it runs just fine. If I don't explicitly call the "public" schema it gives the above error. So, I believe postgis is working just fine, and installed to the public schema, as expected. – Sanjay Advani Apr 4 '18 at 18:45
  • 1
    Solved it for the application's purposes - but without understanding - we had to put public in the search path for the group role, which is inherited by the login role. Doesn't seem like our dev env. database had group or login role search paths. Is that standard and/or best practice to do? – Sanjay Advani Apr 4 '18 at 19:51

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.