1

I am trying to import a shapefile from my QGIS project to my PostGIS schema using DB manager.

I want to create a new table in my public schema and I have an ERROR: relation "spatial_ref_sys" does not exist.

Checking options 'primary key', 'geom column' & 'create spatial index' do not alter the error. Full error message below.

Creation of data source "public"."fwamanc" failed: 

Erroneous query: SELECT AddGeometryColumn('public','fwamanc','geom',27799,'MULTIPOLYGON',2) returned 7

[ERROR:  relation "spatial_ref_sys" does not exist

LINE 1: SELECT SRID         FROM spatial_ref_sys WHERE SRID = new_sr...

^

QUERY:  SELECT SRID         FROM spatial_ref_sys WHERE SRID = new_srid

CONTEXT:  PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,character varying,integer,character varying,integer,boolean) line 48 at SQL statement SQL statement "SELECT public.AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)" PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,integer,character varying,integer,boolean) line 5 at SQL statement ]

I have already created the postgis extension & checked validity of output. This is the first time I have tried to import without restoring via pgdump files.

Anybody help with what I may be missing?

  • What do you mean with "checked validity of output"?, you run the query?. – cabesuon Feb 10 at 16:57
  • Is PostGIS installed in the public schema? – JGH Feb 10 at 18:14
1

The user running the query does not have public in its path.

The 1st query does not throw a not found error because the path is explicitly written (SELECT public.AddGeometryColumn ...)

You can check if the schema where PostGIS is installed is in the user search path by issuing

show search_path;

If not, you can permanently add the path by altering the user.

ALTER USER username SET search_path TO "$user", public;

As the previous command takes effect at the next login only, you can apply it immediately by applying

SET search_path TO "$user", public;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.