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

[Bo Guo]

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.

Bo

On Mon, Nov 27, 2023 at 5:15 AM Matt Gibbins via postgis-users <
postgis-users at lists.osgeo.org> wrote:

> 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.
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20231127/c5bdd1af/attachment.htm>