[postgis-users] Fwd: 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: image.png]


Best regards,
Lan Pham
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200702/a6aad649/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 24153 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200702/a6aad649/attachment.png>