Importing Shapefile to PostGIS using QGIS DB Manager

Question

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?

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