Find IDS not present in table

Question

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

the where not IN will return every id in the table that is not in this array, i just want the bunch of ids in the array that is not in the table — PirateApp, Jan 01 '20 at 07:02
You have an answer below. Get those ids into a table of their own. — Tim Biegeleisen, Jan 01 '20 at 07:03

score 5 · Accepted Answer · answered Jan 01 '20 at 09:53

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
)

score 1 · Answer 2 · answered Jan 01 '20 at 07:00

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)

Find IDS not present in table

2 Answers2