8

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
  • 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, 2018 at 18:38

2 Answers 2

7

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.

2
  • 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. Apr 4, 2018 at 18:45
  • 2
    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? Apr 4, 2018 at 19:51
3

Remove (drop) the postgis extension associated with the public schema.

drop extension postgis

Create a new postgis extension with the name of your schema

create extension postgis with schema <schema_name>

Create Extension Synopsis for postgres

CREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema_name ] [ VERSION version ] [ FROM old_version ]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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