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.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit |
SELECT 1
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);
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
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 | 2024-04-23 11:10:29.832765 | 0 |
| 2 | test2 | 1 | -1 | 2024-04-23 11:10:29.832765 | 0 |
SELECT 2
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 | 2024-04-23 11:10:29.832765 | 0 | 0 |
| 2 | test2 | 1 | 2024-04-23 11:10:29.832765 | -1 | 0 |
SELECT 2