Postgis installation: type “geometry” does not exi

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

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

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 4

You must enable the extension on your database.

psql my_database -c "CREATE EXTENSION postgis;"

Answer 5

Or...

cursor.execute('create extension postgis')

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

Answer 6

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