QGIS - Split a line having attributes with UNIQUE constraint

Question

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

Answer 1

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.

The Attributes table will appear as:

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

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:

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:

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.