[Qgis-user] Editing a Postgis view

Wed Nov 16 09:12:35 PST 2016

It didn't remember to ask "what version?".
Anyway I think the problem could be due to the sequence.
When you use a "serial" data type, you are asking to the database to create
an integer column with a linked sequence.
When you create a view on the table, it does not know how to deal with the
integer column. A rule or a trigger would inform the view about its desired
behaviour, i. e. when you have no value for the primary key, just squeeze
the sequence for a new one.
c

On Wed, Nov 16, 2016 at 4:29 PM, Andrea Peri <aperi2007 at gmail.com> wrote:

> Hi Carlo.
> Thx for response.
>
> I 'm testing better.
> But the strange is that the first write was regulrly executed.
> If the view was really read-only it should refuse also the first write.
>
> Instead only from the second write I have the error.
>
> And the qgis don't report me a "readonly view.
> But instead a "id primary key cannot be nul" constraint violation.
>
> I try to understand better all of this.
>
> Regards,
>
> A.
>
>
> 2016-11-16 15:54 GMT+01:00 Carlo A. Bertelli (Charta s.r.l.)
> <carlo.bertelli at gmail.com>:
> > Ciao Andrea,
> > by default Postgresql views are read only. Even the simplest view made in
> > Postgresql needs a rule to be updated. After writing a statement for a
> view,
> > just take the habit of writing the rules for insert, update, delete.
> > Please read the chapter on the Rule System in the Postgresql Reference
> > Manual for the instructions.
> > c
> >
> > Message: 6
> > Date: Wed, 16 Nov 2016 09:39:23 +0100
> > From: Andrea Peri <aperi2007 at gmail.com>
> > To: Matthias Kuhn <matthias at opengis.ch>
> > Cc: qgis-user <qgis-user at lists.osgeo.org>
> > Subject: Re: [Qgis-user] Editing a Postgis view
> > Message-ID:
> >         <CABqTJk8Bo7fs6qOG=MhO9N50qO5L+JkXAOAgik=tUGWwcKd
> -WA at mail.gmail.com>
> > Content-Type: text/plain; charset=UTF-8
> >
> > Hi Matthias,
> > thx for response.
> > So the trouble is in my db.
> >
> > Now I try to understand better why it is not working.
> >
> > This is my actual try test:
> >
> > create table schemaapp.repository(
> >  ID serial primary key NOT NULL,
> >  UUID varchar(50) NOT NULL,
> >  PROGETTO varchar(9) NOT NULL,
> >  PROCEDIMENTO text NOT NULL,
> >  GEOMETRIA1 geometry(MULTIPOINT, 3003)
> > );
> >
> > create view schemaapp.v_repository_point as
> >  select
> >   a.id as id,
> >   a.uuid as uuid,
> >   a.progetto as progetto,
> >   a.procedimento as procedimento,
> >   a.geometria1 as geometria
> > from
> >   schemappa.repository_wfs as a
> > where
> >   a.geometria1 is not null;
> >
> > I will know if discover something.
> > thx again.
> >
> > 2016-11-16 8:54 GMT+01:00 Matthias Kuhn <matthias at opengis.ch>:
> >> Hi Andrea,
> >>
> >> It works here.
> >> Do you have an instead of insert rule written on your view?
> >> It works for me this way (but my views are too complicated for postgres
> >> to create default insert rules by default anyway).
> >> You can also try to alter your view with a default value for the column
> >> in question.
> >>
> >> Best regards
> >> Matthias
> >>
> >> On 11/16/2016 08:15 AM, Andrea Peri wrote:
> >>> Hi,
> >>> I'm having some trouble to edit a postgis view using qgis.
> >>>
> >>> The view is a simple:
> >>> select list-of-field from table1;
> >>>
> >>> And the table1 is a table with a Primary key on a serial type field.
> >>>
> >>> I add the first record and all was ok. The commit too.
> >>> When go to add the second record.
> >>> I start to have trouble.
> >>> The qgis report my always a constraint violation of the pk.
> >>> I try to leave the pk field empty, or try to fill it with a value.
> >>> No solution work.
> >>>
> >>> So my question is if is confirmed that qgis (I use the 2.18) version)
> >>> is working in editing with a postgis view .
> >>>
> >>> Thx.
> >>> A.
> >>>
> >>>
> >>>
> >> _______________________________________________
> >> Qgis-user mailing list
> >> Qgis-user at lists.osgeo.org
> >> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> >> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
> >
> >
> > _______________________________________________
> > Qgis-user mailing list
> > Qgis-user at lists.osgeo.org
> > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
>
> --
> -----------------
> Andrea Peri
> . . . . . . . . .
> qwerty àèìòù
> -----------------
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20161116/a3b3e5ee/attachment.html>