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

[Paolo Cavallini]

HHhi all,
thanks Alessandro for fixing it. Any hope to backport it to 3.4? This
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/