From: | Gustavsson Mikael <mikael(dot)gustavsson(at)smhi(dot)se> |
---|---|
To: | Durumdara <durumdara(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | SV: Which records aren't in list? Use static list as table or records |
Date: | 2019-05-22 13:51:40 |
Message-ID: | 89DE7C43D727C04CA77C8B7AB82533CD02556B9E04@WINVMSERV464.ad.smhi.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
You can use generate_series.
select generate_series(1,select max(id) from theserecords)
EXCEPT
select id from theserecords;
KR
________________________________
Från: Durumdara [durumdara(at)gmail(dot)com]
Skickat: den 22 maj 2019 15:43
Till: Postgres General
Ämne: 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
From | Date | Subject | |
---|---|---|---|
Next Message | Gustavsson Mikael | 2019-05-22 13:55:20 | SV: Which records aren't in list? Use static list as table or records |
Previous Message | Durumdara | 2019-05-22 13:43:17 | Which records aren't in list? Use static list as table or records |