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.

Supersaturated

Answer 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.

Shithead

Yes Craig, the extension is already created, and what other schemas? i only added postGIS extension in my database – Supersaturated

Please run the exact commands shown above in psql and edit your question to show the output. – Shithead

The problem is well solved, thank you so much for your help, the problem wasn't in PostGis extension, the problem actually was in PGAdmin 4, this version doesn't support geometry type, but when i created the table from Query tools, it worked so well !! – Supersaturated

If it helps anyone here, 'Query Tool' is an option inside of PgAdmin4. So, from within PgAdmin4, 1) create a new database, 2) Right-click on the new db name, 3) select 'Query Tool', 4) run 'create extension postgis;' from witin the Query Tool UI, 5) right click again on the db name, and select 'Restore", and then 6) proceed to load your GIS data. Hope this helps!! – Attested

Answer 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>

Brisance

Answer 3

Just to cover my scenario of encountering this error, in case it helps anyone else...

I was renaming my public schema to public_backup then restoring a dump to a recreated public schema.

However on rename, the postgis extensions were staying associated with public_backup.

You should be able to follow the official PostGIS guidance here to re-associate it back to the new public schema.

Note: Unless you are working with an AWS RDS hosted instance like me (where you won't have permission to perform that trick owing to the fact it was installed as the locked down 'rdsadmin' role:

ERROR: permission denied for table pg_extension

In the end, I abandoned the rename strategy and just overwrote the public schema.

Lambskin