4

I'm using PG 12.2 with PostGIS 3.0.0, and I've created a table with a generated column:

CREATE TABLE public.test1
(
    id integer NOT NULL DEFAULT nextval('test1_id_seq1'::regclass),
    geom geometry(Polygon,3857),
    area_sqrm double precision GENERATED ALWAYS AS (st_area(geom)) STORED,
    CONSTRAINT test1_pkey PRIMARY KEY (id)
)

I want to create new features in this table using QGIS 3.12.2. Therefore I did the following steps:

  1. Import table into QGIS project
  2. Toggled editing
  3. Sketched new valid geometry polygon entity
  4. Approved QGIS default form
    QGIS default form

  5. Saved layer edits

This last step resulted with the following error:

Could not commit changes to layer test1

Errors: ERROR: 1 feature(s) not added.

  Provider errors:
      PostGIS error while adding features: ERROR:  column "geom" does not exist
    LINE 1: ...") VALUES (st_geomfromwkb($1::bytea,3857),st_area(geom)) RET...
                                                                 ^
    HINT:  There is a column named "geom" in table "test1", but it cannot be referenced from this part of the query.

After omitting the default value QGIS applied for the area_sqrm column, I tried to save again. This resulted with the following error:

Could not commit changes to layer test1

Errors: ERROR: 1 feature(s) not added.

  Provider errors:
      PostGIS error while adding features: ERROR:  cannot insert into column "area_sqrm"
    DETAIL:  Column "area_sqrm" is a generated column.

How can I create new features using QGIS, and let their generated columns to be populated DB-wise?

In this case, it can be thought as working with ArcGIS' FGDB feature class which has two dedicated computed geometry fields: shape_area; shape_length. In its environment, the two fields are being computed after creation and altered after the entity's geometry is edited.

9
  • 1
    What if you leave both fields blank? Apr 22, 2020 at 14:54
  • @HeikkiVesanto I'm still getting the same errors.
    – EranGeo
    Apr 22, 2020 at 14:59
  • 1
    does it work with the keyword DEFAULT? In pure SQL it is allowed to have insert/update on generated columns with this keyword
    – JGH
    Apr 22, 2020 at 15:11
  • If I understand you right, @JGH, after assigning DEFAULT to QGIS attribute form default value setting, I'm getting the same first error, that is: Could not commit changes to layer test1 Errors: ERROR: 1 feature(s) not added. Provider errors: PostGIS error while adding features: ERROR: column "geom" does not exist LINE 1: ...") VALUES (st_geomfromwkb($1::bytea,3857),st_area(geom)) RET... ^ HINT: There is a column named "geom" in table "test1", but it cannot be referenced from this part of the query
    – EranGeo
    Apr 22, 2020 at 15:21
  • 1
    what if you write default in the feature attribute form (the 1st image in your post)
    – JGH
    Apr 22, 2020 at 15:25

1 Answer 1

0

This is now possible in the development version (and soon in QGIS 3.14) due the efforts made in this PR. One can now save features of tables containing generated columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.