ST_GeomFromText not found

Question

I installed Postgres 9.5. Then I installed PostGIS. Then I created database named geodb. In it I created column with type polygon and several others like id.

Table polygon was created successfully, so it's seems that PostgreSQL know about geometry.

But when I am trying to run next query:

INSERT INTO public.rasters_previews( id, "Path", "Coordinates", "Name", "imageBounds") VALUES (1, 'foo', ST_GeomFromText('polygon((60.536 27.589,60.509 39.054,54.908 38.183,54.93 28.368,60.536 27.589))'), 'xname', 'xbound');

I am getting error that function ST_GeomFromText does not exists.

I tried to do same with setting postgis_22_sample as template but got same result.

What I am doing wrong? Should I do any other manipulation to get DB wotk as geospatial?

Answer 1

Is the schema PostGIS was installed into in your search path? The easiest way to test is by running the command:

SELECT PostGIS_Full_Version();

If that returns information on PostGIS you're good to go, if not find the schema you installed PostGIS into and add it to the search path. I usually install PostGIS to its own schema with:

CREATE SCHEMA postgis;
CREATE EXTENSION postgis WITH SCHEMA postgis;
ALTER DATABASE geodb SET search_path TO public, postgis;

Don't forget to add any other schemas in your database to the search path. If you install the topology extension it will create it's own topology schema that will need to go into the search path as well.

Answer 2

You can re-install your extensions in the public schema if you accidentally installed them in a schema outside of the default search path

for example:

DROP EXTENSION IF exists postgis;
CREATE EXTENSION postgis WITH SCHEMA public;

DROP EXTENSION IF exists "uuid-ossp";
CREATE EXTENSION "uuid-ossp" WITH SCHEMA public;

DROP EXTENSION IF exists unaccent;
CREATE EXTENSION unaccent WITH SCHEMA public;

DROP EXTENSION IF exists cube;
CREATE EXTENSION cube WITH SCHEMA public;

DROP EXTENSION IF exists ltree;
CREATE EXTENSION ltree WITH SCHEMA public;

DROP EXTENSION IF exists tablefunc;
CREATE EXTENSION tablefunc WITH SCHEMA public;