QGIS: Unable to add a new feature when joining two PostGIS tables

Question

I have two Postgresql tables: parent and child, as the following:

CREATE SEQUENCE public.parent_id_seq;

CREATE TABLE public.parent
(
    id integer NOT NULL DEFAULT nextval('public.parent_id_seq'::regclass),
    type character varying COLLATE pg_catalog."default",
    CONSTRAINT parent_pkey PRIMARY KEY (id)
);

CREATE SEQUENCE public.child_id_seq;

CREATE TABLE public.child
(
    id integer NOT NULL DEFAULT nextval('public.child_id_seq'::regclass),
    name character varying COLLATE pg_catalog."default",
    parent_id integer,
    CONSTRAINT child_pkey PRIMARY KEY (id),
    CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id)
        REFERENCES public.parent (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

In QGIS I have made a join between these two tables (as shown in the attached image). But I am not able to add new features from the attribute table. When I do so I got the following error:

Could not commit changes to layer child

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

  Provider errors:
      PostGIS error while adding features: ERROR:  invalid input syntax for integer: "nextval('public.parent_id_seq'::regclass)"
    LINE 1: ...ild"("id","name","parent_id") VALUES ($1,'name 9','nextval('...

How to Reproduce

1- Create these two tables, as described above.

2- Add them to QGIS.

3- Right click on the parent table, then select properties, then from the Joins tab create a new Join as shown in the attached screen shot.

4- Then allow editing both layers.

5- After that open the attribute table of the parent layer, then select Add Feature, and fill in the type and name fields.

6- Save the parent layer, it will be saved with no problems. Then save the child layer, it will generate the error mentioned above.

I am using QGIS 3.10

Answer 1

As the tables are defined, there is a one-to-many relation between them. You must include that relation in the Project Properties, in the Relations tab.

You can Discover Relations to automatically define it from the PostgreSQL source connection.

---

In the Data Source tab of the Project Properties tick on the options: Automatically create transaction groups when possible and Evaluate default values on the provider side.

Related tables will be saved and edited as a group. And the sequences will be evaluated by the PostgreSQL server.

---

Now you can start creating features in the parent layer and in the child layer. The forms are fully customizable. If you want to create a new child feature which parent feature is not already created, you can tick on the Allow adding new features option, in the parent_id widget, from the Attributes Form of the Layer Properties.

I define the Drag and drop designer type to order the parent_id widget before the name widget. I also modify the parent_id widget to show type value instead of id value. The value stored is an integer, I am just seeing it as the type string in the Attribute Form. It includes the attribute table, the identify form and the create new feature form.

---

Now, when you create a child feature you can create a parent feature instead of choose one.

---

To see a joined table, there are many ways. I just like the way that Virtual layers are refreshed:

SELECT p.type, c.name
FROM parent p
INNER JOIN child c
ON p.id = c.parent_id;  

---

References:

• https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/attribute_table.html#creating-one-or-many-to-many-relations

• https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/vector_properties.html#attributes-form-properties

• https://docs.qgis.org/3.4/en/docs/user_manual/managing_data_source/create_layers.html#creating-virtual-layers

Answer 2

The issue is that QGIS uses the default value for parent.id, which is not a numerical value but instead the call to the sequence. When saving the parent table, this expression is translated to a number and everything is fine.

When saving the child table, QGIS did not yet accessed the updated parent.id value, but instead is still using the default expression (nextval('public.parent_id_seq'::regclass)), which fails.

In fact, looking at the logs (PostGIS tab), we can see that QGIS has some issues to even display the data for the new record, tryind to query on ... WHERE ("parent_id" = 'nextval(''parent_id_seq''::regclass)')

WARNING    1 cursor states lost.
SQL: DECLARE qgis_56 BINARY CURSOR FOR SELECT "id","parent_id"::text FROM "public"."child" WHERE ("parent_id" = 'nextval(''parent_id_seq''::regclass)') LIMIT 1
Result: 7 (ERROR: invalid input syntax for integer: "nextval('parent_id_seq'::regclass)"
LINE 1: ...::text FROM "public"."child" WHERE ("parent_id" = 'nextval('...

One option is to populate the parent data, save the table (which changes the parent.id to a number) then populate the child table and save it. To see the updated child data (id) you may want to un-select the option cache join layer in a virtual layer. It is a pain to do with many back and forth between editing/saving and it is error prone. The solution provided by @Gabriel De Luca (editing 2 tables separately) is much simpler.

Another option is to manually write the parent.id value, populate fields for both the parent and child table, and at last save both tables. A big issue is that you need to reset the parent_id sequence when you are done.

There is a third option that will makes editing very easy but it harder to set-up is to create a view in Postgres doing the join between the two tables, then to set instead of triggers for add/edit/delete on the view, which propagate the changes to the underlying tables. You would work with this view in Qgis as if it was a regular layer