Postgis installation: type “geometry” does not exist

Question

I am trying to create table with Postgis. I do it by this page. But when I import postgis.sql file, I get a lot of errors:

ERROR:  type "geometry" does not exist

Does anybody know how can I fix it?

Answer 1

I had the same problem, but it was fixed by running following code

CREATE EXTENSION postgis;

In detail,

1. open pgAdmin
2. select (click) your database
3. click "SQL" icon on the bar
4. run "CREATE EXTENSION postgis;" code

Answer 2

If the Postgis-Extension is loaded, then your SQL perhaps does not find the geometry-type because of missing search-path to the public schema.

Try

SET search_path = ..., public;

in the first line of your scsript. (replace ... with the other required search-paths)

Answer 3

You can do it from terminal:

psql mydatabasename -c "CREATE EXTENSION postgis";

Answer 4

To get psql to stop on the first error, use -v ON_ERROR_STOP=1 (which is off by default, which is why you see many errors). For example:

psql -U postgres -d postgis -v ON_ERROR_STOP=1 -f postgis.sql

The actual error is something like "could not load library X", which can vary on your situation. As a guess, try this command before installing the sql script:

ldconfig

(you might need to prefix with sudo depending on your system). This command updates the paths to all system libraries, such as GEOS.

Answer 5

This error may also occur if you try to use postgis types on another schema rather than public.

If you are creating you own schema, using postgis 2.3 or higher and encounter this error, do the following as stated here:

CREATE SCHEMA IF NOT EXISTS my_schema;
CREATE extension postgis;

UPDATE pg_extension 
  SET extrelocatable = TRUE 
    WHERE extname = 'postgis';

ALTER EXTENSION postgis 
  SET SCHEMA my_schema;

ALTER EXTENSION postgis 
  UPDATE TO "2.5.2next";

ALTER EXTENSION postgis 
  UPDATE TO "2.5.2";

SET search_path TO my_schema;

Then you can proceed to use postgis functinalities.

Answer 6

You must enable the extension on your database.

psql my_database -c "CREATE EXTENSION postgis;"

Answer 7

You also need to ensure that the user you are trying to use the postgis extension as, has access to the schema where postgis is setup (which in the tutorials I read is called 'postgis').

I just had this error, and it was solved because I had only given a new user access to the database. In the database I'd created, I ran:

grant all on schema postgis to USERNAME; 

And this solved this error

Answer 8

The answers here may solve your problem, however if you already have postgis enabled on your DB, the issue may be that you are trying to restore a postgis table (with a geometry column) into a schema other than where your postgis extension is enabled. In pgAdmin you can click on the postgis extension and see which schema is specified. If you are trying to restore a table with geometry column into a different schema, you might get this error.

I resolved this by altering my postgis extension - however I'm not sure if that was necessarily the best way to do it. All I know is that it allowed me to restore the table.

Answer 9

Or...

cursor.execute('create extension postgis')

in your python program, using a current cursor from psycopg2.

Answer 10

First make sure you have (matching to pg version: psql -V) postgis installed:

sudo apt install postgis postgresql-9.6-postgis-2.3

Just before tables creation add:

db.engine.execute('create extension postgis') 
db.create_all()