3

I am using QGIS with a PostgreSQL/PostGIS database, where the lines table (named 'segments'), besides the integer serial primary key, has a UUID column having a UNIQUE constraint. Trying to split a line with the SplitFeatures tool of the Advanced Digitizing toolbar fails to commit to the database because the new line that is created after the split, has the same uuid of the original line and therefore violates the database constraint.

In order to simulate the way the primary key is generated using the PostgreSQL sequence generator

nextval('segments_segment_id_seq'::regclass)

, I have tried to manually set a default value in the database using a random uuid generated by:

uuid_generate_v5(uuid_ns_url(), (('www.seed.com'::text || '/'::text) || (md5(((random())::text || (clock_timestamp())::text)))::cstring)) 

but this does not work. QGIS continues to duplicate the uuid of the original line to both lines that occur after the split. (Tested that after removing the UNIQUE constraint)

Also tried to include the uuid column into the primary key definition, along with the integer serial. Did not work either.

I assume that QGIS must have a way to identify such columns with constraints when splitting features, because it handles properly the auto-increment of the primary key.

Environment: QGIS 2.8.5-Wien, PostgreSQL 9.4

3
  • I have the same problem: a postgis table like this: gid | integer | non null preimpostato nextval('fogn_tronchi_gid_seq'::regclass) anno | numeric(10,0) | codice_via | numeric(10,0) | diametro | numeric(10,0) | id_n1 | character varying(255) | id_n2 | character varying(255) | materiale | character varying(255) | nome_via | character varying(255) | note | character varying(255) | pendenza | numeric | pend_ind | numeric | proprieta | character varying(255) | qfondo_mon | numeric | qfondo_val | numeric | regime | character varying(255) | stato_cons | character varying(255) | tipologia | character Jun 27, 2017 at 13:05
  • Welcome to GIS:SE @LucaPauluzzi! Please ask this as a new question instead of posting it as an answer. You can always add a link to this post in your question to show you did some research :)
    – Joseph
    Jun 27, 2017 at 13:24
  • This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. You can also add a bounty to draw more attention to this question once you have enough reputation. - From Review
    – mgri
    Jun 27, 2017 at 13:47

1 Answer 1

3

The behaviour of QGIS has been modified in recent versions. New test environment with QGIS 3.32.3-Lima and Postgresql 16.1. Consider a table:

CREATE TABLE public."logged" (
    id serial4 NOT NULL,
    "name" varchar NULL,
    uid uuid NULL DEFAULT uuid_generate_v5(uuid_ns_url(), ('www.seed.com'::text || '/'::text) || md5(random()::text || clock_timestamp()::text)::cstring),
    geom public.geometry(linestring, 2100) NULL,
    CONSTRAINT logged_pkey PRIMARY KEY (id),
    CONSTRAINT logged_unique UNIQUE (uid)
);

Load the table as a layer in QGIS and draw a line. In the form, type only a name and leave the "id" and "uid" fields to be filled automatically.

feature form

The Attributes table will appear as:

attributes table before save

Save layer. QGIS saves into the database and refreshes the table, which now appears as:

attributes table after save

This shows that both the primary key of the table "id" and the uuid "uid" field are properly updated.

Now use the Split Features tool to split the line into two pieces. QGIS creates a new record with the exact "name" field but the "uid" field is the expression that we want. The attributes table now looks like this:

attributes table before save

This is the main diffence with older QGIS versions, where the "uid" field used to have the exact same value as the original record. This caused the duplicate error because "uid" should be UNIQUE. Now, QGIS uses properly the expression stored as DEFAULT in the table/column definition in the database, i.e.

uuid_generate_v5(uuid_ns_url(), ('www.seed.com'::text || '/'::text) || md5(random()::text || clock_timestamp()::text)::cstring)

Note that QGIS throws the following warning after the split. Probably this has to do with QGIS trying to use the "uid" field as text instead of uuid. This warning does not cause any problems though. It needs some further investigation...

2024-02-02T13:08:18     WARNING    Query: BEGIN READ ONLY;DECLARE qgis_163 BINARY CURSOR FOR SELECT "id","name"::text,"uid"::text FROM "public"."logged" WHERE ("uid" = 'uuid_generate_v5(uuid_ns_url(), ((''www.seed.com''::text || ''/''::text) || (md5(((random())::text || (clock_timestamp())::text)))::cstring))') LIMIT 3 returned 7 [ERROR: invalid input syntax for type uuid: "uuid_generate_v5(uuid_ns_url(), (('www.seed.com'::text || '/'::text) || (md5(((random())::text || (clock_timestamp())::text)))::cstring))"
             LINE 1: ..."uid"::text FROM "public"."logged" WHERE ("uid" = 'uuid_gene...
              ^
             ]
2024-02-02T13:08:18     WARNING    1 cursor states lost.
             SQL: BEGIN READ ONLY;DECLARE qgis_163 BINARY CURSOR FOR SELECT "id","name"::text,"uid"::text FROM "public"."logged" WHERE ("uid" = 'uuid_generate_v5(uuid_ns_url(), ((''www.seed.com''::text || ''/''::text) || (md5(((random())::text || (clock_timestamp())::text)))::cstring))') LIMIT 3
             Result: 7 (ERROR: invalid input syntax for type uuid: "uuid_generate_v5(uuid_ns_url(), (('www.seed.com'::text || '/'::text) || (md5(((random())::text || (clock_timestamp())::text)))::cstring))"
             LINE 1: ..."uid"::text FROM "public"."logged" WHERE ("uid" = 'uuid_gene...
              ^
             )

Save and finally the attributes table shows as:

attributes table after split and save

Please note that with this method you can split only one line at a time, before saving the layer. If you have several lines having as uid the same expression, QGIS considers it as a violation of the unique constraint. It seems that it checks the expression instead of the value, which is a behaviour different that the one used for the primary key, where the "nextval()" expression does not cause any violation! The same behaviour happens also when you create new features. Therefore, split or create, save layer and proceed to the next operation

As a conclusion, although this method now works, a better solution could be implemented using triggers or rules, such as here, especially if the database contains additional integrated functionality. It would be best practice to avoid providing access to fields from within QGIS, that are automatically manipulated by the database, such as primary keys filled by a sequence etc. Unfortunately, those fields should be visible to the GIS user most of the times. Updateable database views could help, exposing only the fields to be accessed by the GIS user, but this may be overkill for simple applications and also requires database expertise that is not readily available to GIS engineers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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