I have a postgres database with a postGIS extension. Some of the fields in the database tables are primary keys that are automatically being generated either with a UUID genarator or based on another column. Now, within a postgres/SQL IDE with a 'before insert on' trigger function this is no problem because it is generating the UUID before the insert.

However, I want to achieve the same within QGIS. But because of the NOT NULL characteristic of the PK I can't leave this field empty and thus not being able to save it. Is there a way to solve this?

  • Have you tried to use a dummy default UUID? The value would be overwritten by your trigger
    – JGH
    Nov 23, 2020 at 14:55
  • @JGH good idea, but most of these fields are referencing UUID values from other tables via foreign keys. I can't insert random UUID's, it will error that it can't be found in the parent table. Nov 23, 2020 at 15:29
  • Does the UUID field need to be PK or could it be another unique field?
    – user30184
    Nov 23, 2020 at 15:40
  • @user30184 it could be another I guess Nov 24, 2020 at 10:30

1 Answer 1


A simple solution would be to

  1. proxy your DDLs through a 'working' View having an INSTEAD OF trigger in place:

    CREATE VIEW <main_table>_view AS (
      SELECT <uuid>,
             geom::GEOMETRY(<TYPE>, <SRID>)     -- explicitly forcing registration of the geom column
      FROM   <main_table>
    CREATE OR REPLACE FUNCTION <main_table>_view_insert_function()
        SELECT fk.<uuid>
        FROM   <fk_table> AS fk
        WHERE  fk.<link> = NEW.<link>
        INTO   NEW.id
        INSERT INTO <main_table>
          VALUES (NEW.*)
    CREATE TRIGGER <main_table>_view_insert_trigger
      ON <main_table>
      EXECUTE FUNCTION <main_table>_view_insert_function()
  2. deactivate Enforce not null constraint. in QGIS:

    <Layer properties> | Attributes Form | Available Widgets | Fields | <uuid>

    While the field is still alarmingly red then, the OK button is active.

The idea is to only ever load and work with that View in QGIS; this allows for plenty other things in terms of access control and data quality validation. UPDATES and DELETES should work without any triggers, since the View itself is updateable.


You will want to add


to all BEFORE INSERT trigger (or add the respective filter in the function itself if this trigger does more complex data manipulation) if you need to keep them along with the working View, to remove it from the trigger chain when inserting via the View. While it does no harm, it will essentially duplicate the operation.

Alternatively, you can rewrite the Views trigger function to explicitly insert into all but the PK column, and have the BEFORE INSERT trigger handle the case:

CREATE OR REPLACE FUNCTION <main_table>_view_insert_function()
    INSERT INTO <main_table>(<link>, ..., geom)
      VALUES (NEW.<link>, ..., NEW.geom)

Personally, I'd go the non-chaining way, as

  • you only want to fire that BEFORE INSERT trigger on missing <uuid> values anyways (except it does more complex insertion logic, of course)
  • trigger chains add overhead, and can become hard to debug

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.