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

[Régis Haubourg]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20200211/f94db299/attachment.html>