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:
- Import table into QGIS project
- Toggled editing
- Sketched new valid geometry polygon entity
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.