Postgis installation: type "geometry" does not exist

postgresql geometry postgis

91,999

Solution 1

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

CREATE EXTENSION postgis;

In detail,

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

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

Solution 3

You can do it from terminal:

psql mydatabasename -c "CREATE EXTENSION postgis";

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

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

View more solutions

91,999

Author by

yetty

Updated on November 27, 2021

Comments

yetty over 2 years
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?
Yang over 8 years

Thanks. BTW, If someone using 'psql' to run this code, make sure the role have 'Superuser' attribute/privilege.
James111 almost 8 years

LIFE SAVER! I thought I'd already set this up so I kept thinking it was something else...
LandP almost 7 years

Note that this is specific to a particular database that you are using. It is not installation-wide.
Jon Gretar over 6 years

I put a pg_dump back, and had the same problem. My postgis extension puts it's data into a custom SCHEME, called postgis. I needed to run the following command, and everything was well again: DO $$ BEGIN EXECUTE 'alter database '||current_database()||' set search_path = "$user", public, lookup, postgis'; END; $$ ;
Daniël Tulp almost 6 years

until Postgis version 2.3 you could move the extension to another schema which solved this problem for me a few times
OrangeDog almost 3 years

Only USAGE is required. Do not grant ALL unless you mean it.
Nick K9 over 2 years

This worked for me. I just had to change the statement slightly to CREATE EXTENSION IF NOT EXISTS postgis so that it wouldn't fail on subsequent launches.
Gloria Chen almost 2 years

Note: Add this to your migration file before $this->addSql('CREATE TABLE .... will work.
Vignesh Sundaramoorthy almost 2 years

In my case I had to install sudo apt install postgresql-10-postgis-scripts this before creating extension