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

[Alessandro Pasotti]

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>
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>
> 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> 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
>>> QGIS.ORG Chair:
>>> http://planet.qgis.org/planet/user/28/tag/qgis%20board/
>>> _______________________________________________
>>> QGIS-Developer mailing list
>>> 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
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20200211/b488fa5a/attachment.html>