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

[Régis Haubourg]

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> 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>
> 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/ca7150dc/attachment-0001.html>