Question 89773 in PostgreSQL Question posted in
The official documentation can be found here.

How can I query NOT IN from an array? – Postgresql

 const friendIds = friends.rows.map((friend) => friend.friend_id);

 console.log("list", friendIds); //list [ 50, 51 ]

 const users = await pool.query(
        "SELECT * FROM super_user WHERE user_id NOT IN(ARRAY[$1])",
            [friendIds]
        );

I want to query all users where the user_id does not equal any of the items in the array.

for some reason, even chat GPT is unable to give me a good solution

Tags:

3

Answers


  1. 0

    you can use <> ALL()

    SELECT * 
FROM super_user 
WHERE user_id <> ALL(ARRAY[$1])
    Login or Signup to reply.
  2. 0

    By Using ALL

    SELECT * FROM 
super_user 
WHERE user_id != ALL(ARRAY[$1])

    By Using ANY

    SELECT * FROM 
super_user 
WHERE NOT (user_id = ANY(ARRAY[$1]))
    Login or Signup to reply.
  3. 0

    In the doc, there’s 9.24. Row and Array Comparisons showing examples of this: online demo

    SELECT * FROM super_user WHERE not user_id = SOME(ARRAY[$1]);
SELECT * FROM super_user WHERE user_id <> ALL(ARRAY[$1]);

    In 9.19. Array Functions and Operators you can find it’s also possible to just unnest() the array and compare to the resulting list of elements or see if the array_position() of your element is null:

    SELECT * FROM super_user WHERE not user_id in (select * from unnest(ARRAY[$1]));
SELECT * FROM super_user WHERE array_position(ARRAY[$1], user_id) is null;

    Good to know what happens if you get a null on either side:

    select null=any(array[1,   2]),--null, not false
       null=any(array[null,2]),--null, even though it's technically there
       1   =any(array[null,2]),--null, not false
       1   =any(array[null,1]);--true, even though there was a null to compare to
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search