<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<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:#2f2f2f;padding:0px 0px 0px 2px;">
<div
style="color:#aaaaaa;background-color:#2f2f2f;font-family:"Monospace";font-size:10pt;white-space:pre;"><p
style="margin:0;"><span
style="color:#739eca;font-weight:bold;">CREATE</span> <span
style="color:#739eca;font-weight:bold;">TABLE</span> <span
style="color:#9e9e9e;">maps</span>.<span
style="color:#9e9e9e;">routelines</span> (</p><p
style="margin:0;"> <span style="color:#9e9e9e;">tc_line_id</span> <span
style="color:#9e9e9e;">serial4</span> <span
style="color:#739eca;font-weight:bold;">NOT</span> <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">tc_date</span> <span
style="color:#c1aa6c;font-weight:bold;">date</span> <span
style="color:#739eca;font-weight:bold;">NOT</span> <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">shape_id</span> <span
style="color:#c1aa6c;font-weight:bold;">varchar</span>(<span
style="color:#c0c0c0;">10</span>) <span
style="color:#739eca;font-weight:bold;">NOT</span> <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">route_id</span> <span
style="color:#c1aa6c;font-weight:bold;">varchar</span>(<span
style="color:#c0c0c0;">10</span>) <span
style="color:#739eca;font-weight:bold;">NOT</span> <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">route_short_name</span> <span
style="color:#c1aa6c;font-weight:bold;">varchar</span>(<span
style="color:#c0c0c0;">15</span>) <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">route_long_name</span> <span
style="color:#c1aa6c;font-weight:bold;">varchar</span>(<span
style="color:#c0c0c0;">255</span>) <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">route_desc</span> <span
style="color:#c1aa6c;font-weight:bold;">varchar</span>(<span
style="color:#c0c0c0;">50</span>) <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">route_type</span> <span
style="color:#c1aa6c;font-weight:bold;">int2</span> <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">direction_id</span> <span
style="color:#c1aa6c;font-weight:bold;">int2</span> <span
style="color:#739eca;font-weight:bold;">NOT</span> <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">both_ways</span> <span
style="color:#c1aa6c;font-weight:bold;">bool</span> <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span style="color:#9e9e9e;">geom</span> <span
style="color:#9e9e9e;">public</span>.<span
style="color:#9e9e9e;">geometry</span> <span
style="color:#739eca;font-weight:bold;">NOT</span> <span
style="color:#739eca;font-weight:bold;">NULL</span>,</p><p
style="margin:0;"> <span
style="color:#739eca;font-weight:bold;">CONSTRAINT</span> <span
style="color:#9e9e9e;">routelines_pk</span> <span
style="color:#739eca;font-weight:bold;">PRIMARY</span> <span
style="color:#739eca;font-weight:bold;">KEY</span> (<span
style="color:#9e9e9e;">tc_date</span>, <span
style="color:#9e9e9e;">shape_id</span>)</p><p
style="margin:0;">)<span style="color:#eecc64;">;</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:#2f2f2f;padding:0px 0px 0px 2px;">
<div
style="color:#aaaaaa;background-color:#2f2f2f;font-family:"Monospace";font-size:10pt;white-space:pre;"><p
style="margin:0;"><span
style="color:#739eca;font-weight:bold;">select</span> <span
style="color:#9e9e9e;">tc_date</span>,<span
style="color:#9e9e9e;">shape_id</span>,<span
style="color:#9e9e9e;">direction_id</span>,<span
style="color:#9e9e9e;">route_id</span>,(<span
style="color:#9e9e9e;">st_dumppoints</span>(<span
style="color:#9e9e9e;">geom</span>)).* </p><p
style="margin:0;"> <span
style="color:#739eca;font-weight:bold;">from</span> <span
style="color:#9e9e9e;">maps</span>.<span
style="color:#9e9e9e;">routelines</span></p><p
style="margin:0;"> <span
style="color:#739eca;font-weight:bold;">where</span> <span
style="color:#9e9e9e;">route_type</span><=<span
style="color:#c0c0c0;">3</span></p><p style="margin:0;"> <span
style="color:#739eca;font-weight:bold;">group</span> <span
style="color:#739eca;font-weight:bold;">by</span> <span
style="color:#9e9e9e;">tc_date</span>,<span
style="color:#9e9e9e;">shape_id</span>,<span
style="color:#9e9e9e;">direction_id</span>,<span
style="color:#9e9e9e;">route_id</span><span
style="color:#eecc64;">;</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:#2f2f2f;padding:0px 0px 0px 2px;">
<div
style="color:#aaaaaa;background-color:#2f2f2f;font-family:"Monospace";font-size:10pt;white-space:pre;"><p
style="margin:0;"><span
style="color:#739eca;font-weight:bold;">drop</span> <span
style="color:#739eca;font-weight:bold;">table</span> <span
style="color:#739eca;font-weight:bold;">if</span> <span
style="color:#739eca;font-weight:bold;">exists</span> <span
style="color:#9e9e9e;">routelines_not_school</span><span
style="color:#eecc64;">;</span></p><p style="margin:0;"><span
style="color:#739eca;font-weight:bold;">create</span> <span
style="color:#739eca;font-weight:bold;">temp</span> <span
style="color:#739eca;font-weight:bold;">table</span> <span
style="color:#9e9e9e;">routelines_not_school</span> <span
style="color:#739eca;font-weight:bold;">as</span> <span
style="color:#739eca;font-weight:bold;">select</span> * <span
style="color:#739eca;font-weight:bold;">from</span> <span
style="color:#9e9e9e;">maps</span>.<span
style="color:#9e9e9e;">routelines</span> <span
style="color:#739eca;font-weight:bold;">where</span> <span
style="color:#9e9e9e;">route_type</span><=<span
style="color:#c0c0c0;">3</span><span
style="color:#eecc64;">;</span> <span
style="color:#669768;">-- exclude school bus services</span></p><p
style="margin:0;"> </p><p style="margin:0;"><span
style="color:#739eca;font-weight:bold;">select</span> <span
style="color:#9e9e9e;">tc_date</span>,<span
style="color:#9e9e9e;">shape_id</span>,<span
style="color:#9e9e9e;">direction_id</span>,<span
style="color:#9e9e9e;">route_id</span>,(<span
style="color:#9e9e9e;">st_dumppoints</span>(<span
style="color:#9e9e9e;">geom</span>)).* </p><p
style="margin:0;"> <span
style="color:#739eca;font-weight:bold;">from</span> <span
style="color:#9e9e9e;">routelines_not_school</span></p><p
style="margin:0;"> <span
style="color:#739eca;font-weight:bold;">where</span> <span
style="color:#9e9e9e;">route_type</span><=<span
style="color:#c0c0c0;">3</span></p><p style="margin:0;"> <span
style="color:#739eca;font-weight:bold;">group</span> <span
style="color:#739eca;font-weight:bold;">by</span> <span
style="color:#9e9e9e;">tc_date</span>,<span
style="color:#9e9e9e;">shape_id</span>,<span
style="color:#9e9e9e;">direction_id</span>,<span
style="color:#9e9e9e;">route_id</span><span
style="color:#eecc64;">;</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>
</body>
</html>