CODEWITHSUNDEEP
sqlpostgresqlwhere-in
PirateApp
PirateApp

Reputation: 6204

Find IDS not present in table

I have a bunch of md5 ids

6c26e28dc6484f0e998fd18b883de4c6
2bf4d0d85709c75adba13ba48011d62c
a67a5bcf329d58d2c23ed422214f66b3
...

Some of them are present in the table and some of them are new. How do I find which of these ids are not present in table If I do

select id from items where id not iN (...)

it then excludes all the ids

If I do

select id from items where id IN (...)

it only gives the ids that are present and I have to programmatically find the ones not present How do I directly get the IDS not present

Database: postgresql

Upvotes: 2

Views: 2185

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272096

You can use the VALUES clause which is a standard and available in most RDBMS:

SELECT src.id
FROM (VALUES
    ('6c26e28dc6484f0e998fd18b883de4c6'),
    ('2bf4d0d85709c75adba13ba48011d62c'),
    ('a67a5bcf329d58d2c23ed422214f66b3')
) AS src(id)
WHERE NOT EXISTS (
    SELECT 1
    FROM items 
    WHERE items.id = src.id
)

Upvotes: 5

zip
zip

Reputation: 4061

You want to get among the bunch, those that are not present in items. I would turn the md5 into a table with unnest and string_to_array

select * from
( select
unnest(string_to_array('6c26e28dc6484f0e998fd18b883de4c6,
2bf4d0d85709c75adba13ba48011d62c,
a67a5bcf329d58d2c23ed422214f66b3', ','))as md5
)  a
where not exists(select 1 from items i where id = md5)

Upvotes: 1

Related Questions