0

I have a table (african_primates_17) set up in PostGIS and I have a view (finalised) generated from that. The view is then added into QGIS 3. The simple idea was to overwrite the table by importing from an updated shapefile - and then expect the QGIS map to be miraculously updated also. However, that did not work. I get the following error

Error 2
Creation of data source "public"."african_primates_17" failed: 
ERROR:  cannot drop table african_primates_17 because other objects depend on it
DETAIL:  view finalised depends on table african_primates_17
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT:  SQL statement "DROP TABLE IF EXISTS public.african_primates_17 RESTRICT"
PL/pgSQL function dropgeometrytable(character varying,character varying,character varying) line 15 at EXECUTE

Can anyone explain my error please? I thought the whole principle of views was for updates to be automatically made.

  • 1
    my guess is you should be able to update the base table the view depends on with an update or insert query but overwriting that table is probably not allowed – ziggy Jun 26 at 14:13
  • I suspect there lies my lack of knowledge. You are pointing out I am trying to overwrite my table - not allowed. So how do I merely update or insert? – IanGS Jun 26 at 21:56
3

There are two different concepts here.

1) a view will reflect the underlying table(s) content. If you add/delete/update a row, the changes will be immediately seen in the view.

ex: add a record to "public"."african_primates_17" and it will appear through the view in QGIS (after panning or refreshing the map)

2) a view depends on its underlying table(s). It cannot exist without them. If you drop the underlying table, the view cannot exists. While some systems (Oracle) allows broken views, Postgres doesn't.

Since your function is dropping the table, you would have to also drop the view and recreate it. Alternatively, if you want to keep the view intact, don't drop the table but just truncate it (remove all of its data)

  • I'm looking at a way of developing multiple layers/etc in QGIS from this single all-embracing table. But given the nature of the research, that table will have multiple tweaks to it over time. I thought that by keeping all the data in a csv file (opened with Excel) I could easily overwrite the Postgis table and viola! All my layers would be intact but updated. Is there a better way? – IanGS Jun 26 at 16:10
  • 1
    Your overall approach is fine. Just don't drop the table,or re-create the view – JGH Jun 26 at 17:09
  • Haha - excuse my lack of expertise. I don't want to have to regenerate the VIEWS. So how do I update the TABLE without dropping it? – IanGS Jun 26 at 20:38
  • I've truncated the "african_primates_17" which has kept the table but removed the contents. Excellent. But I don't know how to insert the shapefile, I only know how to import/overwrite the shapefile which of course drops the "african_primates_17" table. (From DB Manager) – IanGS Jun 27 at 13:35
2

You can't drop a table that has a dependency (view) built from it.

Instead, I would set up an OGR Foreign data wrapper (FDW) on the shapefile.

Then instead of dropping the table, first delete all records, then set up an insert statement to pull from the FDW.

You're then free to update the shapefile as needed, and as long as the columns in the shapefile don't change, you don't have to alter the FDW or the DELETE & INSERT statements.

You can even use python to script the entire process.

  • Another technical leap for me. I've not found anything that begins to set out (in less technical terms) how to do this. Do you know of any such tutorial on "setting up an OGR Foreign data wrapper (FDW)"? I want to extract from a shapefile or a csv file or an Excel file? – IanGS Jun 28 at 12:02
  • 1
    Let me post next week on this thread what we use... – DPSSpatial Jun 28 at 22:22
  • @IanGS - before I go further, what server are you using for your PostgreSQL database? We're using CentOS, but want to make sure I post correct instructions based on your server... – DPSSpatial Jul 1 at 15:23
  • I have it set up on my desktop (Windows 10). – IanGS Jul 1 at 15:52
  • 1
    OK cool. Let me get you some info... – DPSSpatial Jul 1 at 16:10

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.