Home > mailing lists

Thread: 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

From

Durumdara

Date:

22 May 2019, 16:43:17

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)

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:

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

From

Gustavsson Mikael

Date:

22 May 2019, 16:51:40

Hi,

You can use generate_series.

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

select id from theserecords;

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)

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:

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

From

Gustavsson Mikael

Date:

22 May 2019, 16:55:20

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;

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)

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:

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

From

Patrick FICHE

Date:

22 May 2019, 16:59:15

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)

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:

Attachment

image002.png