Postgresql error: column must appear in the GROUP BY clause or be used in an aggregate function

Question

Message table from where i want to get recent contacts for 'user1', now the table looks like this:

| id | receiver | sender | time       |
|----|----------|--------|------------|
| 1  | user1    | host1  | 2020-07-02 |
| 2  | host2    | user1  | 2020-07-03 |
| 3  | user3    | host3  | 2020-07-04 |

I managed to get the expected result by using mysql:

SELECT CASE WHEN receiver = 'user1' THEN sender ELSE receiver END AS id, max(time) AS time 
FROM message WHERE receiver = 'user1' OR sender = 'user1' 
GROUP BY CASE WHEN receiver = 'user1' THEN sender ELSE receiver END;

But when i run this query on postgres i get the error: Postgresql error: column receiver must appear in the GROUP BY clause or be used in an aggregate function. Any solution to fix this problem?

Your query works as written in PostgreSQL: db-fiddle.com/f/c648pLyq1N4yQTDcBMUfeM/0 — Mike Organek
– Mike Organek, Commented Jul 14, 2020 at 16:30
yes, but when i use it on node-postgres module i get that error — Pho
– Pho, Commented Jul 14, 2020 at 16:32

Gordon Linoff · Accepted Answer · 2020-07-14 16:29:12Z

2

I would suggest distinct on:

select distinct on ( case when receiver = 'user1' then sender else receiver end ) m.*
from messages m
where 'user1' in (receiver, sender)
order by (case when receiver = 'user1' then sender else receiver end), time desc;

answered Jul 14, 2020 at 16:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql error: column must appear in the GROUP BY clause or be used in an aggregate function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related