PostgreSQL query not working in QGIS Graphical Modeler

Question

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

Answer 1

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

• aidena: 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.
• sallib: Is your unique id an integer ?
• aidena: Yes, the unique id is an integer.
• sallib: Did you try to write the SQL function without a CTE expression, just replacing it by a nested query ?