2

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

enter image description here

  • That isn't the way to add the postgres relation to QGIS. Why do you want to join the tables? – Gabriel De Luca Nov 30 '19 at 21:06
  • 1
    the error message indicates that the id and parent_id are swapped. The sequence should populate child.id and $1 should be the parent_id – JGH Nov 30 '19 at 21:27
  • @JGH $1 is the value of the child_id_sequence. When the parent table is saved, another (maybe $1) value is stored in the parent.id field, but the child table don't know it and try to store the default nextval('public.parent_id_seq'::regclass) value in its parent_id field. – Gabriel De Luca Nov 30 '19 at 21:37
  • ... but it is not the default for this field! – JGH Nov 30 '19 at 21:42
  • yes it is, in the join he/she is making it. – Gabriel De Luca Nov 30 '19 at 21:44
2

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.

1

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.

2

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.

3

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.

4


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;  

5


References:

  • thanks a lot for your detailed reply. Actually the reason for joining these two tables is that the relation between them is one-to-one, and I want to be able to view/add/update/delete the two related records using one screen (the attribute table screen of the parent). – devfaz Dec 1 '19 at 10:48
  • @devfaz A one-to-one realtion is defined with a UNIQUE constraint in the foreign key. You can View/Add/Update/Delete records of related tables from a Form, maybe you want an unified form to manage two tables. Trying to edit features of the child table from the parent table is just not the way to do it, I don't know if the default value inconsistency created can be solved. – Gabriel De Luca Dec 1 '19 at 11:03
  • @gebriel de luca, how to create a unified form to manage the two tables? – devfaz Dec 1 '19 at 11:24
  • @devfaz I don't know it, I like to have one form for each table. But you can ask it in a new question. – Gabriel De Luca Dec 1 '19 at 11:30
1

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

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.