Postgresql – How to structure database in order to lookup and store post ID’s in one PostgreSQL command


I have a table with user info (each user has a unique SERIAL ID), a posts table (each post also has a unique SERIAL ID, and references the ID of the user who posts it), a following table with two columns, each is a user id. Then there is a viewed table with a user id column and a post id column, so a user won't see a post twice.

I currently have an API endpoint /feed and it performs two PostgreSQL commands. The first one gets the last five most recent posts from the accounts that a user follows, but I want the user to not see a post twice, so next I store the ID's of the posts that are sent in the viewed table. I want this to be in one single PostgreSQL command. How could I achieve this?

Best Answer

you can use join table for example

select, p.userid, p.content
from post p
join userinfo ui on
order by created_date desc
limit 5

or you can use CTE feature (imagine two table as one table)