PostGIS functions do not exist even when public is in the search path

Question

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?

Answer 1

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.

Answer 2

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 ]