How to find out if a specific set of ids exists in DB in Postgres

Question

I've got the following table:

userId|imageId|
------|-------|
     1|      1|
     1|      2|
     1|      3|

and in a special validator I want to make sure that those imageIds I'm given (let's say, 2 and 3) exist in this table and belong to a user with userId = 1;

How do I do that in Postgres?

My PostgreSQL is 12.3.

Sabareesh · Accepted Answer · 2020-07-15 20:02:03Z

1

Fetch count of records that matched:

select count(imageId) from table_name where userId=1 and imageId in (2, 3);

Check if count matches the length of given imageId list and return true or false based on that:

if query.first()[0] >= len(imageIdList):
     return True
return False

edited Jul 15, 2020 at 20:02

answered Jul 15, 2020 at 19:45

Sabareesh

7611 gold badge8 silver badges14 bronze badges

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

2 Comments

Albert Over a year ago

yeah basically in my case I can do something like: select uii."imageId" from user_images_image uii where uii."userId" = 1 and uii."imageId" in (1, 5, 4); but it will return 1, that's to say it will return those ids that do exist, indeed then I could compare the result to the initial list of ids and based on the result of this comparison return true or false in my validator but is there a less verbose way to do that? Ideally, I would like to return something if all of the specified ids are in the DB and return nothing if at least one of them is missing. Is there a way to achieve that?

Sabareesh Over a year ago

You could do that by comparing the number of records that matched and number of imageId's that were given. See my updated answer.

Collectives™ on Stack Overflow

How to find out if a specific set of ids exists in DB in Postgres

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