SV: Which records aren't in list? Use static list as table orrecords - Mailing list pgsql-general

From Gustavsson Mikael
Subject SV: Which records aren't in list? Use static list as table orrecords
Date
Msg-id 89DE7C43D727C04CA77C8B7AB82533CD02556B9E14@WINVMSERV464.ad.smhi.se
Whole thread Raw
In response to SV: Which records aren't in list? Use static list as table orrecords  (Gustavsson Mikael <mikael.gustavsson@smhi.se>)
List pgsql-general
Here is one without syntax error.

select generate_series(1,(select max(id) from theserecords))
EXCEPT
select id from theserecords;


Från: Gustavsson Mikael [mikael.gustavsson@smhi.se]
Skickat: den 22 maj 2019 15:51
Till: Durumdara; Postgres General
Ämne: SV: Which records aren't in list? Use static list as table or records

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@gmail.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

pgsql-general by date:

Previous
From: Gustavsson Mikael
Date:
Subject: SV: Which records aren't in list? Use static list as table orrecords
Next
From: Patrick FICHE
Date:
Subject: RE: Which records aren't in list? Use static list as table or records