Обсуждение: Which records aren't in list? Use static list as table or records

Поиск
Список
Период
Сортировка

Which records aren't in list? Use static list as table or records

От
Durumdara
Дата:
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

SV: Which records aren't in list? Use static list as table orrecords

От
Gustavsson Mikael
Дата:
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

SV: Which records aren't in list? Use static list as table orrecords

От
Gustavsson Mikael
Дата:
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

RE: Which records aren't in list? Use static list as table or records

От
Patrick FICHE
Дата:

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

 

01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg

 

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

Вложения