type "geometry" does not exist, but it exists

Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

type "geometry" does not exist, but it exists

pham lan
Hello all,

I would like to ask for your help in understanding a postgres issue that I could not find out till now.
So I have a postgres dump file of 159G created by pg_dump that I want to restore to my database. It is running well till it throws me the following error:

$ psql -a -U postgres -d mydb < /opt/postgres_restore_files/db_mydb_2020-06-11_18-00.dmp
.....
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR:  type "geometry" does not exist
LINE 1: SELECT geog::geometry
                     ^
QUERY:  SELECT geog::geometry
CONTEXT:  PL/pgSQL function public._togeom(public.geography) line 3 at RETURN
STATEMENT:  CREATE INDEX my_index ON mydb.geo_zone USING gist (public._togeom(geom));
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

I already looked and tried the comment on similar issue in the following links but it does not help:
https://stackoverflow.com/questions/6850500/postgis-installation-type-geometry-does-not-exist

 Below are the definitions: CREATE FUNCTION public._togeom(geog public.geography) RETURNS public.geometry LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN geog::geometry; END; $$; ALTER FUNCTION public._togeom(geog public.geography) OWNER TO postgres; CREATE TABLE mydb.geo_zone ( ... geom public.geography, ... ); ALTER TABLE mydb.geo_zone OWNER TO my_user; .... CREATE INDEX my_index ON mydb.geo_zone USING gist (public._togeom(geom));

I can see type geometry exists in public schema after creating extension postgis in mydb, not sure why it throws that error during the restore procedure. Also I tried afterward to run the CREATE INDEX command again manually and it works without any error and I can see the index "my_index" created. But of course i don't want this manual fix, just try to understand what causes the issue.

Any idea from you is highly appreciated! Thanks.

image.png


Best regards,
Lan Pham

_______________________________________________
Europe mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/europe