I am in the process of populating a PostGIS database from a bunch of shapefiles loaded in a QGIS project. The field names of the shapefiles don't match those in the database so it is not possible to use the built-in loader. So I was trying to use an INSERT query in the DB Manager to do so. However, it seems that the DB manager has either a DB scope or a virtual layer scope, and it does not seem possible to copy from a project file to a database table by mapping the field names to each other.

I used a query that goes along this form:

INSERT INTO public.table (field1, field2, geom) 
SELECT field_one, field_two, geometry FROM project_layer

Is there a workaround that allows using a SQL query in the DB manager to INSERT features from a project layer into a PostGIS table?


You could look at Refactor fields tool in the QGIS processing toolbox. With that you can map the fields in your shapefile to match the ones in your table. Load the target table to your project and use that as the template layer and use your shapefiles as the input layer. You can also use QGIS expressions to convert data on the fly if necessary. The tool then creates a temporary (or on disk) file from your mapped data which can be uploaded to PostGIS with the built-in tools.

Refactor fields

  • I'm aware of the Refactor Fields but I believe it's just another way if not an extra step of how I was addressing the problem. I'm essentially loading the shapefiles as is in a temporary table in PostGIS then using a query there to copy to the target table. I guess the functionality I'm looking for is not yet implemented in QGIS. Thanks for the suggestion though! :)
    – Techie_Gus
    May 23 '21 at 7:56

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.