Postgresql filter in relation

Question

Table "Post":

id	active
382857	true
382851	true
383015	true
382906	true
382909	true

Table "PosTags":

post_id	tag_id
382857	480
382857	953
382857	9230
382857	9232
382857	56677
382857	107901

The first table stores posts, while the second one stores postags for each post. I need to gather information regarding posts and tags and I'm using the following query:

SELECT id 
FROM posts
WHERE id NOT IN (SELECT post_id FROM post_tags WHERE tag_id = 55723)
LIMIT 8

Problem is that this query won't retrieve posts that have no postags.

Can you help me fixing this query?

Can you please attach output of EXPLAIN and DDL you've used to create mentioned tables? — agulowaty
– agulowaty, Commented May 27, 2022 at 10:41
QUERY PLAN Limit (cost=460.44..460.90 rows=8 width=5) -> Seq Scan on pgvk_material (cost=460.44..11135.67 rows=187409 width=5) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Index Scan using fki_pgvk_material_tag_id_fkey on pgvk_material_tag (cost=0.43..460.13 rows=124 width=4) Index Cond: (tag_id = 55723) — Brain2xml
– Brain2xml, Commented May 27, 2022 at 10:57
create table posts ( id bigserial primary key, ..... ); create table post_tags ( post_id bigint not null constraint post_tags_post_id_foreign references posts on update cascade on delete cascade, tag_id bigint not null constraint post_tags_tag_id_foreign references tags on update cascade on delete cascade, primary key (post_id, tag_id) ); — Brain2xml
– Brain2xml, Commented May 27, 2022 at 11:01

lemon · Accepted Answer · 2022-05-27 11:08:20Z

1

Try with this query:

SELECT posts.id,
       posts.active
FROM      posts
LEFT JOIN posttags
       ON posts.id = posttags.post_id
      AND posttags.tag_id = 9230
WHERE posttags.post_id IS NULL

Basically it looks for the posts which have the specific tag_id you don't want and excludes them from the match of the posts table using a LEFT JOIN.

If you have more than one tag you want to exclude, it is sufficient to change the second part of the JOIN condition into the following:

AND posttags.tag_id IN [9230, ...]

SQL Fiddle here.

answered May 27, 2022 at 11:08

lemon

15.6k6 gold badges23 silver badges42 bronze badges

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

2 Comments

Brain2xml Over a year ago

exelent solution

lemon Over a year ago

If this answer has helped you, consider ticking it as the solution of your post. @Brain2xml

Collectives™ on Stack Overflow

Postgresql filter in relation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related