[Qgis-user] Unique IDs in a PostGIS view

[Karl Magnus Jönsson]

Hi!
I’ve used something like this to get unique Ids:
SELECT row_number() OVER (ORDER BY s.omrade_id, s.kod) AS id, …. ORDER BY s.omrade_id, s.kod;

If the ordering isn’t necessary I guess you can skip that.

https://www.postgresql.org/docs/current/static/functions-window.html


Karl-Magnus Jönsson

Från: Qgis-user [mailto:qgis-user-bounces at lists.osgeo.org] För Árni Geirsson
Skickat: den 12 april 2017 11:34
Till: qgis-user at lists.osgeo.org
Ämne: [Qgis-user] Unique IDs in a PostGIS view

I am trying to create a view that extracts vertices from lines using ST_DumpPoints and to get unique IDs I have set up a sequence to generate the numbers using nextval(). The query executes normally in the DB Mananger and I see the results as a table in the table view. However, when I attempt to load the results of the view as a layer in QGIS, I get an error: "Database error: ERROR:  cannot execute nextval() in a read-only transaction". No features are loaded.
Are there any smart tricks out there to work around this or other means of generating the IDs?

Árni Geirsson
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20170412/a6f3e82c/attachment.html>