I opened a PostGIS layer (from the server) with QGIS and I did some modifications (for example attribute join from another layer). How can I save my modifications so that it is visible in pgAdmin?
Add a comment
|
4 Answers
TL;DR: You can modify data physically present inside individual tables, but you cannot alter their columnar structure (or row counts) based on a join of multiple data sources.
Make sure you understand the conceptual basics of the data structures you are mentioning, and the implications of using them within a client application like QGIS.
PostgreSQL, as an RDB, stores data as a variable set of rows over a pre-defined range of columns (tabular structure, i.e. tables), possibly related to other tabular data by key references (i.e. relations).
In the simplest form, QGIS loads these tables as layers, but you can add data from the DB as layers in arbitrary relational complexity via loading query results. Given the former, your layer is nothing more than a reference to the (now locally cached) data that lives in a particular table in the DB.
Now, it is simple to modify the actual data that lives in the table via editing the layer - rows within the tabular data are uniquely identified via keys, and their (columnar) values can then be replaced (i.e. updated) when saving the edits.
An attribute join is a completely different operation, and it is not a modification - as the name suggests, it joins together two (or more) layers (which may not even all live inside the DB as tables - QGIS allows for joins of arbitrary data sources inside the project environment). In effect, the joined set of rows contain columnar values from multiple sources, which cannot be mapped to the pre-defined column structure of the base table. In addition, if a single row of one table happens to be joined to multiple rows of the other table/layer, row count increases and the unique key references to the base table rows are lost.
If all participating layers exists as tables in the DB, manage them there - joins are among the most idiomatic mechanics in RDBs; you can either
- create composite Views of them inside the DB and load them into the QGIS project, or
- use the respective SQL query results (DB Manager) as layers directly.
Note here that only Views are able to get modified - but with multiple participating tables this has to be implemented via triggers in the DB.
If the joined layer is from a different data source you will have to either
- import them into the DB to be able to work with Views as outlined above
- use a Virtual Layer that acts as a dynamic wrapper around the defined join inside QGIS, or
- export the join result into a physical data storage of your choice
As soon as you save your edits in a PostGIS layer, the modifications will be committed to PostGIS, so that it will be visible in any other tools (e.g. pgAdmin).
Although, if you do a attribute join in QGIS, that is just a use of the data that will not be reflected back into the database. (remember, you can do an attribute join between any type of data sources that may or may not be visible for a PostGIS database) For that to be visible in the database, you would have to do it "the other way around", make the join as a view in the database and then open that view in QGIS.
answered Sep 1, 2021 at 10:10
Might I add here that if you have full permissions on the DB in question, you could join your data on QGIS, delete the existing table in the DB, and re-upload the data into a table by the same name, you could even drag and drop. This is usually how I accomplish tasks that require the processing of data that lives in a DB.
Due to the complicated nature of views, they are often not "update-able". To make some views editable, you have to create an INSTEAD OF trigger that "ON UPDATE" for the view telling PostgreSQL explicitly what to do when you modify the results of a view. This of course must be completed in PostgreSQL not as a join in QGIS
