Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


create table post (postid int, title varchar, author int, created timestamp);
create table votes (postid int, username varchar, vote int);

insert into post values (1,'test1',1,now());
insert into post values (2,'test2',1,now());

insert into votes values(1,'A',1);
insert into votes values(1,'B',-1);
insert into votes values(2,'B',-1);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT 
post.postID as postID, 
post.title as title, 
post.author as author,
COALESCE(sum(votes.vote), 0) as voteCount, 
post.created as created,
COALESCE(sum(votes.vote) filter (where votes.username= '1'), 0) as voteCount

       FROM post LEFT JOIN votes ON post.postID = votes.postID 
       GROUP BY 1,2,3,5 ORDER BY 4 DESC


postid
title
author
votecount
created
votecount


1
test1
1
0
2020-10-15 11:26:05.491617
0

2
test2
1
-1
2020-10-15 11:26:05.491617
0


SELECT 
    p.postID as postID, 
    p.title as title, 
    p.author as author,
    p.created as created,
    COALESCE(sum(v1.vote), 0) as voteCount, 
    COALESCE(v2.vote , 0) as userVote  -- in '' just provide username for current login user
FROM post p
LEFT JOIN votes v1 ON p.postID = v1.postID 
LEFT JOIN votes v2 on p.postID = v2.postID and v2.username='username'
GROUP BY 1,2,3,4,v2.vote
ORDER BY 5 DESC


postid
title
author
created
votecount
uservote


1
test1
1
2020-10-15 11:26:05.491617
0
0

2
test2
1
2020-10-15 11:26:05.491617
-1
0

postid	title	author	votecount	created	votecount
1	test1	1	0	2020-10-15 11:26:05.491617	0
2	test2	1	-1	2020-10-15 11:26:05.491617	0