Updating views from QGIS doesn't work as I expect it to work.
I've got the following (simplified) tables, views and triggers:
CREATE TABLE public.parcels ( id serial, geom geometry(MultiPolygon,27700), "Land Registry Reference" character varying(30), type integer, area double precision, "Design Issues" text, sys_period tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone), CONSTRAINT parcels_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); CREATE TABLE public.owners ( id integer, "Title Number" character varying, "Proprietor Name" character varying, sys_period tstzrange ) WITH ( OIDS=FALSE ); drop view if exists from_two; CREATE VIEW from_two as( select "parcels"."id" AS "id", "parcels"."Land Registry Reference", "parcels"."geom", "owners"."Proprietor Name", "parcels"."Design Issues", ROW_NUMBER() over() AS uid FROM "public"."parcels" LEFT OUTER JOIN "owners" ON "parcels"."Land Registry Reference" = "owners"."Title Number" ); CREATE OR REPLACE function update_from_two() returns trigger AS $f$ BEGIN UPDATE public.parcels SET id = NEW.id, "Land Registry Reference" = NEW."Land Registry Reference", geom = NEW.geom, "Design Issues" = NEW."Design Issues" WHERE id = OLD.id; UPDATE owners SET "Proprietor Name" = NEW."Proprietor Name" WHERE "Proprietor Name" = OLD."Proprietor Name"; return NEW; END; $f$ LANGUAGE plpgsql; DROP trigger if exists tg_fromtwo on from_two; CREATE TRIGGER tg_fromtwo INSTEAD OF UPDATE ON from_two FOR EACH ROW EXECUTE PROCEDURE update_from_two();
it all works fine and when I update the view in pgadmin with
UPDATE from_two SET id = 244, "Design Issues" = 'None' WHERE id = 244; I also can open this view in Qgis, but when I edit data and save it, the edited data ends up in the wrong table row. Do I have to do something different to be able to update correctly in Qgis?
I'm using Postgres 9.5, Postgis 2.2.2 and QGIS 2.16.2
