GROUP BY partitioned_table.id not working anymore after partitioning

[gitmalong]

Hi!

I created a partitioned table as seen here: https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto.md#simple-time-based-1-partition-per-day

In my old data structure I was able to query

SELECT time_taptest_table.col3, time_taptest_table.col2, SUM(other.number)
FROM time_taptest_table 
INNER JOIN other ON other.col3 = time_taptest_table.col3
GROUP BY col1;

without any issues as col1 was a unique primary key. In the partitioned setup the index does not exist for time_taptest_table.

ERROR:  column "time_taptest_table. col2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:   "time_taptest_table"."col2",
          ^ 

SQL state: 42803
Character: 27

The issue might be related to https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=699586315704a8268808e3bdba4cb5924a038c49

Do I understand correctly that in my example there is no guarantee that col1 is unique across all partitions or is that something that partman guarentees due to the template table?

How can I work around that?

[keithf4]

Apologies that I didn't get back to answering this sooner. You are correct in that there is no guarantee that col1 is unique across the partition set. The template table is just providing a means to be able to define the unique index since it cannot be defined on the parent.

However the error I don't think is related to this. When you do an aggregate, you usually have to do a group by on all the other columns that are not part of the aggregate, in this case sum(). I rewrote this a bit to show a successful run of the query as I think you originally intended (without data)

SELECT t.col3, t.col2, SUM(o.number)
FROM partman_test.time_taptest_table t
INNER JOIN other o ON o.col3 = t.col3
GROUP BY t.col3, t.col2;
 col3 | col2 | sum 
------+------+-----
(0 rows)

[gitmalong]

Thanks for clarifying!!