[QGIS-Developer] Insert data in a table with a table with a GENERATED column

[Alessandro Pasotti]

On Wed, Feb 12, 2020 at 9:05 AM Paolo Cavallini <cavallini at faunalia.it>
wrote:

> HHhi all,
> thanks Alessandro for fixing it. Any hope to backport it to 3.4? This
>


Nope, 3.4 is EOL

there is a bp here for 3.10 though:

https://github.com/qgis/QGIS/pull/34291



raises again the issue of long term releases, and their interaction with
> upgrades of related sw.
> Cheers.
>
> Il 11/02/20 17:15, Régis Haubourg ha scritto:
> > Oh, I see my master is old. I thought I recompiled it last week. I'll
> > check again. Let's compile.
> > Regards
> > Régis
> >
> > Le mar. 11 févr. 2020 à 15:12, Alessandro Pasotti <apasotti at gmail.com
> > <mailto:apasotti at gmail.com>> a écrit :
> >
> >
> >     Wierd, I thought I fixed that: there is a test too,
> >
> https://github.com/qgis/QGIS/pull/34017/files#diff-99b101819133a316786604243f3abf6eR1432
> >
> >     feel free to reopen and provide additional test details or test
> cases.
> >
> >     https://github.com/qgis/QGIS/pull/34017
> >
> >
> >     On Tue, Feb 11, 2020 at 3:05 PM Régis Haubourg
> >     <regis.haubourg at gmail.com <mailto:regis.haubourg at gmail.com>> wrote:
> >
> >         For the record here are my SQL commands:
> >         drop table if exists data.pipe;
> >
> >         CREATE TABLE data.pipe
> >         (
> >             id integer NOT NULL ,
> >             label_1_text character varying(120) COLLATE
> >         pg_catalog."default",
> >             _length2d numeric(8,2) GENERATED ALWAYS AS
> >         (st_length2D(geometry)) STORED,
> >             _length3d numeric(8,2) GENERATED ALWAYS AS
> >         (ST_3DLength(geometry)) STORED,
> >             geometry geometry(LineStringZ,3946) NOT NULL,
> >             CONSTRAINT pipe_pkey PRIMARY KEY (id)
> >         );
> >
> >         INSERT INTO
> >
>  "data"."pipe"("geometry","id","label_1_text","_length2d","_length3d")
> >         VALUES (st_geomfromtext('LINESTRINGZ(1 1 1, 2 2 2)',3946),2
> >         ,'test',DEFAULT, DEFAULT);
> >
> >         Le mar. 11 févr. 2020 à 15:03, Régis Haubourg
> >         <regis.haubourg at gmail.com <mailto:regis.haubourg at gmail.com>> a
> >         écrit :
> >
> >             Hi Paolo,
> >             nice feature, thanks for the heads up !
> >
> >             I confirm the behavior here.
> >
> >             The documentation advises to use the `DEFAULT` keyword into
> >             the INSERT command.
> >             Here is what I get with a QGIS-like insert :
> >
> >              INSERT INTO
> >
>  "data"."pipe"("geometry","id","label_1_text","_length2d","_length3d")
> >             VALUES (st_geomfromtext('LINESTRINGZ(1 1 1, 2 2 2)',3946),1
> >             ,'test',0,  0);
> >             ERREUR:  42601: ne peut pas insérer dans la colonne «
> >             _length2d »
> >             DÉTAIL : Column "_length2d" is a generated column.
> >             EMPLACEMENT : rewriteTargetListIU, rewriteHandler.c : 827
> >
> >             With the keyword 'DEFAULT', it works
> >
> >             INSERT INTO
> >
>  "data"."pipe"("geometry","id","label_1_text","_length2d","_length3d")
> >             VALUES (st_geomfromtext('LINESTRINGZ(1 1 1, 2 2 2)',3946),2
> >             ,'test',DEFAULT, DEFAULT);
> >
> >             And with no data supplied for the generated columns, it also
> >             works
> >
> >             We indeed have to upgrade the postgresql provider to check
> >             this particular column type to handle INSERT and UPDATE
> >             accordingly.
> >
> >             Regards
> >
> >             Régis
> >
> >
> >             Le mar. 11 févr. 2020 à 13:59, Paolo Cavallini
> >             <cavallini at faunalia.it <mailto:cavallini at faunalia.it>> a
> écrit :
> >
> >                 Hi all,
> >                 PostgreSQL 12 has the option of a GENERATED ALWAYS AS
> >                 ... STORED column.
> >                 QGIS is able to load the table, but apparently it is not
> >                 possible to
> >                 insert new records. Does anyone confirm that this is not
> >                 a local problem?
> >                 All the best.
> >                 --
> >                 Paolo Cavallini - www.faunalia.eu <
> http://www.faunalia.eu>
> >                 QGIS.ORG <http://QGIS.ORG> Chair:
> >                 http://planet.qgis.org/planet/user/28/tag/qgis%20board/
> >                 _______________________________________________
> >                 QGIS-Developer mailing list
> >                 QGIS-Developer at lists.osgeo.org
> >                 <mailto:QGIS-Developer at lists.osgeo.org>
> >                 List info:
> >                 https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >                 Unsubscribe:
> >                 https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >
> >         _______________________________________________
> >         QGIS-Developer mailing list
> >         QGIS-Developer at lists.osgeo.org
> >         <mailto:QGIS-Developer at lists.osgeo.org>
> >         List info:
> https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >         Unsubscribe:
> https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >
> >
> >
> >     --
> >     Alessandro Pasotti
> >     w3:   www.itopen.it <http://www.itopen.it>
> >
>
> --
> Paolo Cavallini - www.faunalia.eu
> QGIS.ORG Chair:
> http://planet.qgis.org/planet/user/28/tag/qgis%20board/
>


-- 
Alessandro Pasotti
w3:   www.itopen.it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20200212/b1cd2832/attachment-0001.html>