postgres error 42803 with subquery and group by-CodePudding

Hi all postgres developer,

below code can run success

select p.* from
    (select p.*, count(distinct p1.id) n from TMB p
        left join TMB p1 on p.id = p1.pid
        left join TUR u on p.id = any(u.jks)
        group by p.id) p
    join TUR u on u.id = p.uid

but, below code with error message [42803] ERROR: column "p.xxxx" must appear in the GROUP BY clause or be used in an aggregate function

select p.* from
    (select p.*, count(distinct p1.id) n from (select * from TMB) p
        left join TMB p1 on p.id = p1.pid
        left join TUR u on p.id = any(u.jks)
        group by p.id) p
    join TUR u on u.id = p.uid

I want to do some where filter on TMB table before left join, so I think can speed up left join.

I think (select * from TMB) is a subquery equal as TMB. I can not understand why this error message. anyone can tell me detail?

CodePudding user response：

The difference is that without the subquery, PostgreSQL can deduce that id is the primary key of tmb, so you need not add all columns of tmb to the GROUP BY clause. With the subquery, PostgreSQL cannot make that deduction, so you have to add all columns.