4

Edit: For further context, my table does have a unique id in the form of an integer. Through the database manager, the query executes successfully and I am able to load the query layer into QGIS. This same query however does not work when executed using the "PostgreSQL execute and load SQL" processing tool, which I intend to use for my model in the Graphical Modeler.

I am building a model in the QGIS Graphical Modeler which executes and loads PostgreSQL query. The query works perfectly fine when executed via the database manager, but when I try to run it through the graphical modeler it returns the two errors:

WARNING    Primary key field '"id"' for view/query not unique.
WARNING    PostgreSQL layer has no primary key.

This is the query I successfully executed via the Database Manager:

with mylot as
(select *
from lrs.nsw_lot l
where l.lotnumber in ('17','18')
and l.plannumber in ('24039'))
select b.*
from mylot inner join lrs.nsw_lot b on st_dwithin (mylot.geom, b.geom, 500)
where b.lotnumber not in ('17','18')
or b.plannumber not in ('24039')

And this is the query I'm attempting to execute through the graphical modeler:

'with mylot as
(select *
from lrs.nsw_lot l
where l.lotnumber in (' || @lot_number || ')
and l.plannumber in (' || @dp_number || '))
select b.*
from mylot inner join lrs.nsw_lot b on st_dwithin (mylot.geom, b.geom,' || @radius || ')
where b.lotnumber not in (' ||  @lot_number  || ')
or b.plannumber not in (' || @dp_number  || ')'

These are my inputs when executing from the Graphical Modeler (shouldn't be relevant to my error, but just for extra context):

enter image description here

1 Answer 1

5

You need a unique value to load the layer in QGIS. You could create a unique id with this SQL function :

SELECT row_number() over() as uniq_id
4
  • My apologies, my original statement may have been lacking further information. My table does have a unique id, and I am able to query and load my layer successfully via the database manager. However, when I use the same query in the Graphical Modeler, I receive the aforementioned errors.
    – aidena
    Commented Mar 27, 2023 at 22:54
  • Is your unique id an integer ?
    – sallib
    Commented Mar 28, 2023 at 9:33
  • Yes, the unique id is an integer.
    – aidena
    Commented Mar 28, 2023 at 20:43
  • Did you try to write the SQL function without a CTE expression, just replacing it by a nested query ?
    – sallib
    Commented Mar 30, 2023 at 10:32

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.