🎓 Postgres aggregate functions

BOOL_OR

Comments table

post_id	status
1	published
1	draft
2	published
3	published
3	published
4	draft

SELECT BOOL_OR(comments.status = 'draft')
FROM comments

Output

bool_or
true

SELECT 
	comments.post_id,
	BOOL_OR(comments.status = 'draft' )
FROM comments
GROUP BY comments.course_id

Output

post_id	bool_or
1	true
2	false
3	false
4	true

ARRAY_AGG

Comments table

post_id	status
1	published
1	draft
2	published
3	published
3	published
4	draft

SELECT ARRAY_AGG(comments.status)
FROM comments

Output

array_agg
{published,draft,published,published,published,draft}

SELECT 
	comments.post_id
	ARRAY_AGG(comments.status)
FROM comments
GROUP BY comments.post_id

Output

post_id	array_agg
1	{published,draft}
2	{published}
3	{published,published}
4	{draft}

SELECT 
	comments.post_id
	ARRAY_AGG(DISTINCT comments.status)
FROM comments
GROUP BY comments.post_id

Output

post_id	array_agg
1	{published,draft}
2	{published}
3	{published}
4	{draft}