<div dir="ltr"><div>Matt, this is the expected SQL behavior when group by is used in the selection. See if removing "group by" will return what you need.</div><div><br></div><div>Bo<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Nov 27, 2023 at 5:15 AM Matt Gibbins via postgis-users <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><u></u>
<div>
<p>I have encounter an unexpected result when running
st_dumppoints() with a partitioned dataset. <br>
</p>
<p>In this case the partitioned dataset has the following definition
for the master table partitioned by tc_date</p>
<table width="500" height="250" cellspacing="2" cellpadding="2" border="1">
<tbody>
<tr>
<td valign="top">
<div style="background-color:rgb(47,47,47);padding:0px 0px 0px 2px">
<div style="color:rgb(170,170,170);background-color:rgb(47,47,47);font-family:"Monospace";font-size:10pt;white-space:pre-wrap"><p style="margin:0px"><span style="color:rgb(115,158,202);font-weight:bold">CREATE</span> <span style="color:rgb(115,158,202);font-weight:bold">TABLE</span> <span style="color:rgb(158,158,158)">maps</span>.<span style="color:rgb(158,158,158)">routelines</span> (</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">tc_line_id</span> <span style="color:rgb(158,158,158)">serial4</span> <span style="color:rgb(115,158,202);font-weight:bold">NOT</span> <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">tc_date</span> <span style="color:rgb(193,170,108);font-weight:bold">date</span> <span style="color:rgb(115,158,202);font-weight:bold">NOT</span> <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">shape_id</span> <span style="color:rgb(193,170,108);font-weight:bold">varchar</span>(<span style="color:rgb(192,192,192)">10</span>) <span style="color:rgb(115,158,202);font-weight:bold">NOT</span> <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">route_id</span> <span style="color:rgb(193,170,108);font-weight:bold">varchar</span>(<span style="color:rgb(192,192,192)">10</span>) <span style="color:rgb(115,158,202);font-weight:bold">NOT</span> <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">route_short_name</span> <span style="color:rgb(193,170,108);font-weight:bold">varchar</span>(<span style="color:rgb(192,192,192)">15</span>) <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">route_long_name</span> <span style="color:rgb(193,170,108);font-weight:bold">varchar</span>(<span style="color:rgb(192,192,192)">255</span>) <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">route_desc</span> <span style="color:rgb(193,170,108);font-weight:bold">varchar</span>(<span style="color:rgb(192,192,192)">50</span>) <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">route_type</span> <span style="color:rgb(193,170,108);font-weight:bold">int2</span> <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">direction_id</span> <span style="color:rgb(193,170,108);font-weight:bold">int2</span> <span style="color:rgb(115,158,202);font-weight:bold">NOT</span> <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">both_ways</span> <span style="color:rgb(193,170,108);font-weight:bold">bool</span> <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(158,158,158)">geom</span> <span style="color:rgb(158,158,158)">public</span>.<span style="color:rgb(158,158,158)">geometry</span> <span style="color:rgb(115,158,202);font-weight:bold">NOT</span> <span style="color:rgb(115,158,202);font-weight:bold">NULL</span>,</p><p style="margin:0px"> <span style="color:rgb(115,158,202);font-weight:bold">CONSTRAINT</span> <span style="color:rgb(158,158,158)">routelines_pk</span> <span style="color:rgb(115,158,202);font-weight:bold">PRIMARY</span> <span style="color:rgb(115,158,202);font-weight:bold">KEY</span> (<span style="color:rgb(158,158,158)">tc_date</span>, <span style="color:rgb(158,158,158)">shape_id</span>)</p><p style="margin:0px">)<span style="color:rgb(238,204,100)">;</span></p></div>
</div>
</td>
</tr>
</tbody>
</table>
<p>The following query is successful.</p>
<table width="590" height="80" cellspacing="2" cellpadding="2" border="1">
<tbody>
<tr>
<td valign="top">
<div style="background-color:rgb(47,47,47);padding:0px 0px 0px 2px">
<div style="color:rgb(170,170,170);background-color:rgb(47,47,47);font-family:"Monospace";font-size:10pt;white-space:pre-wrap"><p style="margin:0px"><span style="color:rgb(115,158,202);font-weight:bold">select</span> <span style="color:rgb(158,158,158)">tc_date</span>,<span style="color:rgb(158,158,158)">shape_id</span>,<span style="color:rgb(158,158,158)">direction_id</span>,<span style="color:rgb(158,158,158)">route_id</span>,(<span style="color:rgb(158,158,158)">st_dumppoints</span>(<span style="color:rgb(158,158,158)">geom</span>)).* </p><p style="margin:0px"> <span style="color:rgb(115,158,202);font-weight:bold">from</span> <span style="color:rgb(158,158,158)">maps</span>.<span style="color:rgb(158,158,158)">routelines</span></p><p style="margin:0px"> <span style="color:rgb(115,158,202);font-weight:bold">where</span> <span style="color:rgb(158,158,158)">route_type</span><=<span style="color:rgb(192,192,192)">3</span></p><p style="margin:0px"> <span style="color:rgb(115,158,202);font-weight:bold">group</span> <span style="color:rgb(115,158,202);font-weight:bold">by</span> <span style="color:rgb(158,158,158)">tc_date</span>,<span style="color:rgb(158,158,158)">shape_id</span>,<span style="color:rgb(158,158,158)">direction_id</span>,<span style="color:rgb(158,158,158)">route_id</span><span style="color:rgb(238,204,100)">;</span></p></div>
</div>
</td>
</tr>
</tbody>
</table>
<p>However, when I create a table from maps.routelines as shown
below the query fails with the message 'ERROR: column
"routelines_not_school.geom" must appear in the GROUP BY clause or
be used in an aggregate function'</p>
<p>This is the process which results in the error.</p>
<table width="50%" cellspacing="2" cellpadding="2" border="1">
<tbody>
<tr>
<td valign="top">
<div style="background-color:rgb(47,47,47);padding:0px 0px 0px 2px">
<div style="color:rgb(170,170,170);background-color:rgb(47,47,47);font-family:"Monospace";font-size:10pt;white-space:pre-wrap"><p style="margin:0px"><span style="color:rgb(115,158,202);font-weight:bold">drop</span> <span style="color:rgb(115,158,202);font-weight:bold">table</span> <span style="color:rgb(115,158,202);font-weight:bold">if</span> <span style="color:rgb(115,158,202);font-weight:bold">exists</span> <span style="color:rgb(158,158,158)">routelines_not_school</span><span style="color:rgb(238,204,100)">;</span></p><p style="margin:0px"><span style="color:rgb(115,158,202);font-weight:bold">create</span> <span style="color:rgb(115,158,202);font-weight:bold">temp</span> <span style="color:rgb(115,158,202);font-weight:bold">table</span> <span style="color:rgb(158,158,158)">routelines_not_school</span> <span style="color:rgb(115,158,202);font-weight:bold">as</span> <span style="color:rgb(115,158,202);font-weight:bold">select</span> * <span style="color:rgb(115,158,202);font-weight:bold">from</span> <span style="color:rgb(158,158,158)">maps</span>.<span style="color:rgb(158,158,158)">routelines</span> <span style="color:rgb(115,158,202);font-weight:bold">where</span> <span style="color:rgb(158,158,158)">route_type</span><=<span style="color:rgb(192,192,192)">3</span><span style="color:rgb(238,204,100)">;</span> <span style="color:rgb(102,151,104)">-- exclude school bus services</span></p><p style="margin:0px"> </p><p style="margin:0px"><span style="color:rgb(115,158,202);font-weight:bold">select</span> <span style="color:rgb(158,158,158)">tc_date</span>,<span style="color:rgb(158,158,158)">shape_id</span>,<span style="color:rgb(158,158,158)">direction_id</span>,<span style="color:rgb(158,158,158)">route_id</span>,(<span style="color:rgb(158,158,158)">st_dumppoints</span>(<span style="color:rgb(158,158,158)">geom</span>)).* </p><p style="margin:0px"> <span style="color:rgb(115,158,202);font-weight:bold">from</span> <span style="color:rgb(158,158,158)">routelines_not_school</span></p><p style="margin:0px"> <span style="color:rgb(115,158,202);font-weight:bold">where</span> <span style="color:rgb(158,158,158)">route_type</span><=<span style="color:rgb(192,192,192)">3</span></p><p style="margin:0px"> <span style="color:rgb(115,158,202);font-weight:bold">group</span> <span style="color:rgb(115,158,202);font-weight:bold">by</span> <span style="color:rgb(158,158,158)">tc_date</span>,<span style="color:rgb(158,158,158)">shape_id</span>,<span style="color:rgb(158,158,158)">direction_id</span>,<span style="color:rgb(158,158,158)">route_id</span><span style="color:rgb(238,204,100)">;</span></p></div>
</div>
</td>
</tr>
</tbody>
</table>
<p>This is unexpected. <br>
Any assistance appreciated.</p>
<p>Matt.<br>
</p>
<p><br>
</p>
<p><br>
</p>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>