<div dir="ltr">I left the over clause empty. Based on your suggestion, I tried the following:<div><br></div><div><div>SELECT row_number() over (</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>order by </div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span><a href="http://fillline_a.id">fillline_a.id</a>,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>(st_dumppoints(fillline_a.geom)).path, </div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>st_x((st_dumppoints(fillline_a.geom)).geom),</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>st_y((st_dumppoints(fillline_a.geom)).geom)</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>) as id,</div><div>(fillline_a.z)::real AS z,</div><div>(st_dumppoints(fillline_a.geom)).path[1] as p,</div><div>st_x((st_dumppoints(fillline_a.geom)).geom) as x,</div><div>st_y((st_dumppoints(fillline_a.geom)).geom) as y,</div><div>((st_dumppoints(fillline_a.geom)).geom)::geometry(Point,3057) AS geom</div><div>FROM v0103.fillline_a</div></div><div><br></div><div>The problem here is that everything in the over clause remains constant for each line and therefore the row_number is not incremented for each point. Maybe I am doing something wrong. Anyway, I wonder if it should count as a bug in QGIS that I can use the nextval() method to see the correctly constructed view when listed as a table in DB Manager but not on the map canvas. Anyway, I found a simple way around the numbering problem:</div><div><br></div><div><div>with t as (</div><div>select z, (st_dumppoints(fillline_a.geom)).geom from v0103.fillline_a)</div><div>select row_number() over () as id, z, geom from t</div></div><div><br></div><div>Problem solved :)</div><div>Thanks for the input.</div><div><br></div><div>Árni</div></div><div class="gmail_extra"><br clear="all"><div><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div><br></div><font color="#000000">Árni Geirsson</font><br><span style="color:rgb(102,102,102);font-size:x-small"><font color="#666666"><b>Alta ehf</b> // +354 582 5000 // +354 897 9549</font></span></div></div></div></div></div></div></div></div></div></div></div></div>
<br><div class="gmail_quote">On 12 April 2017 at 12:48, Steve Toutant <span dir="ltr"><<a href="mailto:Steve.Toutant@inspq.qc.ca" target="_blank">Steve.Toutant@inspq.qc.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><font face="Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size="2"><font face="Verdana, Arial, Helvetica, sans-serif">It would help to see how you used row_number and what field you put in it.</font><div style="font-family:Verdana,Arial,Helvetica,sans-serif"><br></div><div style="font-family:Verdana,Arial,Helvetica,sans-serif">You could create row_number after the dumping</div><div><font face="Verdana, Arial, Helvetica, sans-serif">select row_number() OVER (ORDER BY path,st_x,st_y.......) AS id, *</font></div><div><font face="Verdana, Arial, Helvetica, sans-serif"> from </font></div><div><font face="Verdana, Arial, Helvetica, sans-serif">(select .</font><span style="font-family:Verdana,Arial,Helvetica,sans-serif;font-size:12.8px">ST_DumpPoints...</span><span style="font-family:Verdana,Arial,Helvetica,sans-serif;font-size:12.8px">.. from ...)</span></div><div><br><font color="#990099" style="font-family:Verdana,Arial,Helvetica,sans-serif">-----"Qgis-user" <<a href="mailto:qgis-user-bounces@lists.osgeo.org" target="_blank">qgis-user-bounces@lists.<wbr>osgeo.org</a>> a écrit : -----</font><div class="m_8332669204377694753iNotesHistory" style="font-family:Verdana,Arial,Helvetica,sans-serif;padding-left:5px"><div style="padding-right:0px;padding-left:5px;border-left:solid black 2px">A : Karl Magnus Jönsson <<a href="mailto:Karl-Magnus.Jonsson@kristianstad.se" target="_blank">Karl-Magnus.Jonsson@<wbr>kristianstad.se</a>><br>De : Árni Geirsson <u></u><br>Envoyé par : "Qgis-user" <u></u><br>Date : 12/04/2017 07:13<br>Cc: "<a href="mailto:qgis-user@lists.osgeo.org" target="_blank">qgis-user@lists.osgeo.org</a>" <<a href="mailto:qgis-user@lists.osgeo.org" target="_blank">qgis-user@lists.osgeo.org</a>><br>Objet : Re: [Qgis-user] Unique IDs in a PostGIS view<div><div class="h5"><br><br><div dir="ltr">Thanks for the suggestion Karl.<div>I have used row_number() also but in the case of dumping vertex points from a line, multiple points are created from each line feature and therefore, the row_number is repeated for all points on the same line.</div><div><br></div><div>Árni</div></div><div class="gmail_extra"><br clear="all"><div><div class="m_8332669204377694753gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div><br></div><font color="#000000">Árni Geirsson</font><br><span style="font-size:x-small"><font color="#666666"><font color="#666666"><b>Alta ehf</b> // <a href="tel:582%205000" value="+3545825000" target="_blank">+354 582 5000</a> // <a href="tel:897%209549" value="+3548979549" target="_blank">+354 897 9549</a></font></font></span></div></div></div></div></div></div></div></div></div></div></div></div>
<br><div class="gmail_quote">On 12 April 2017 at 09:45, Karl Magnus Jönsson <span dir="ltr"><<a href="mailto:Karl-Magnus.Jonsson@kristianstad.se" target="_blank">Karl-Magnus.Jonsson@<wbr>kristianstad.se</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="SV" link="blue" vlink="purple">
<div class="m_8332669204377694753m_-6758276434687743317WordSection1">
<p class="MsoNormal"><a name="m_8332669204377694753_m_-6758276434687743317_T_Default_Reply"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d">Hi!<u></u><u></u></font></span></a></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d">I’ve used something like this to get unique Ids:<u></u><u></u></font></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d">SELECT row_number() OVER (ORDER BY s.omrade_id, s.kod) AS id, …. ORDER BY s.omrade_id, s.kod;<u></u><u></u></font></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d"><u></u> <u></u></font></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d">If the ordering isn’t necessary I guess you can skip that.<u></u><u></u></font></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d"><u></u> <u></u></font></span></p>
<p class="MsoNormal"><a href="https://www.postgresql.org/docs/current/static/functions-window.html" target="_blank"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif"">https://www.postgresql.org/doc<wbr>s/current/static/functions-win<wbr>dow.html</span></a><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d"><u></u><u></u></font></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d"><u></u> <u></u></font></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d"><u></u> <u></u></font></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Franklin Gothic Book","sans-serif";color:black">Karl-Magnus Jönsson</span></b><span style="font-size:10.0pt;font-family:"Franklin Gothic Book","sans-serif";color:black"><u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><font color="#1f497d"><u></u> <u></u></font></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">Från:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Qgis-user [mailto:<a href="mailto:qgis-user-bounces@lists.osgeo.org" target="_blank">qgis-user-bounces@list<wbr>s.osgeo.org</a>]
<b>För </b>Árni Geirsson<br>
<b>Skickat:</b> den 12 april 2017 11:34<br>
<b>Till:</b> <a href="mailto:qgis-user@lists.osgeo.org" target="_blank">qgis-user@lists.osgeo.org</a><br>
<b>Ämne:</b> [Qgis-user] Unique IDs in a PostGIS view<u></u><u></u></span></p><span>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">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.<u></u><u></u></p>
<div>
<p class="MsoNormal">Are there any smart tricks out there to work around this or other means of generating the IDs?<u></u><u></u></p>
</div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span style="color:black">Árni Geirsson</span><u></u><u></u></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</span></div>
</div>
</blockquote></div><br></div>
</div></div><div><font face="Courier New,Courier,monospace" size="3">______________________________<wbr>_________________<br>Qgis-user mailing list<br><a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br>List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/qgis-user</a><br>Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/qgis-user</a></font></div><u></u><u></u></div></div></div><div><br>Si vous n'êtes pas le destinataire de ce message, veuillez le détruire près avoir informé l'expéditeur de son erreur. Par ailleurs, il est interdit de copier ou de modifier ce courriel sans l'autorisation de l'auteur.<br><br>L'Institut national de santé publique du Québec n'assume aucune responsabilité à l'égard du contenu des messages personnels envoyés par ses employés.<br></div></font>
</blockquote></div><br></div>