[postgis-users] Unexpected interaction with ST_DumpPoints when creating table from conditional query on a partitioned table.

[Matt Gibbins]

I have encounter an unexpected result when running st_dumppoints() with 
a partitioned dataset.

In this case the partitioned dataset has the following definition for 
the master table partitioned by tc_date

CREATE TABLE maps.routelines (

tc_line_id serial4 NOT NULL,

tc_date date NOT NULL,

shape_id varchar(10) NOT NULL,

route_id varchar(10) NOT NULL,

route_short_name varchar(15) NULL,

route_long_name varchar(255) NULL,

route_desc varchar(50) NULL,

route_type int2 NULL,

direction_id int2 NOT NULL,

both_ways bool NULL,

geom public.geometry NOT NULL,

CONSTRAINT routelines_pk PRIMARY KEY (tc_date, shape_id)

);

The following query is successful.

select tc_date,shape_id,direction_id,route_id,(st_dumppoints(geom)).*

from maps.routelines

where route_type<=3

group by tc_date,shape_id,direction_id,route_id;

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'

This is the process which results in the error.

drop table if exists routelines_not_school;

create temp table routelines_not_school as select * from maps.routelines 
where route_type<=3; -- exclude school bus services

select tc_date,shape_id,direction_id,route_id,(st_dumppoints(geom)).*

from routelines_not_school

where route_type<=3

group by tc_date,shape_id,direction_id,route_id;

This is unexpected.
Any assistance appreciated.

Matt.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20231127/1d941576/attachment.htm>