RE: Which records aren't in list? Use static list as table or records - Mailing list pgsql-general
Hi,
May be something like this could help
SELECT TestList.id
FROM ( SELECT * FROM ( VALUES( 1 ), (5), (12), (33), (55) ) t ) AS TestList( id )
LEFT OUTER JOIN idList ON IdList.id = TEstList.id
WHERE IdList.Id IS NULL;
Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96
From: Durumdara <durumdara@gmail.com>
Sent: Wednesday, May 22, 2019 3:43 PM
To: Postgres General <pgsql-general@postgresql.org>
Subject: Which records aren't in list? Use static list as table or records
Hi!
A very silly question. I have a limited list of identifiers.
I want to know which one IS NOT in a table.
The
select * from theserecords where id not in (1, 5, 12, 33, 55)
isn't listing missing records... because they are missing... :-)
For example, a pseudo:
idlist = (1, 5, 12, 33, 55)
select id from idlist
where id not in (select id from theserecords)
The id list is a static string.
Now I can do this with temporary table - I create one, insert the ID-s and run the select:
select id from temptable where id not in (select id from theserecords)
It would be nice if I can get the missing ID-s.
F.e:
select id from (
select 1 as id,
select 5 as id,
...
) where id not in (select id from theserecords)
or
select id from (
select split_string_to_records('1,2,3,4,5', ',') as id
) ...
Do you know any simple way to do this without stored proc or temp table?
Thank you!
B.W:
dd
Attachment
pgsql-general by date: