ERROR: type "public.geometry" does not exist

Question

I'm supposed to create a spatial database and have installed Postgres and added the PostGIS extension.

When I try to add a column of type public.geometry it always gives an error message that

geometry type doesn't exist

I'm not familiar with Postgres, and I don't know what the solution can possibly be. I tried to Google it but found nothing.

Solution 1

Did you CREATE EXTENSION postgis; ? Check \dx in psql.

Did you put the PostGIS extension in some other schema? Check \dT *.geometry in psql.

Solution 2

This may happen when you create the postgis extension outside of your target database, as in, you logged onto your DB server without specifying the database:

psql -h DB_HOST -U DB_USER 
CREATE EXTENSION postgis;
\q
<apply dump here>

This happened to me.

Fix: I needed to DROP the extension from this space, then specify the target DB, then create the postgis extension:

psql -h DB_HOST -U DB_USER DB_NAME
CREATE EXTENSION postgis;
\q
<apply dump here as DB_USER, onto DB_NAME>