Find values not present in supplied list postgres

Question

I am trying to find a query that will tell me the values that are not in my database. Eg:

      select seqID, segment from flu where seqID IN (1,2,3,4,5,6,7,8,9). 

Now if my database doesn't have seqID's 3,8,9 how would I find/display only the missing seqID's.

Answer 1

First, since you appear to be new to Stackoverflow, here's a few etiquette and posting tips:

• Always include relevant version information. Here, you probably want to include PostgreSQL's version info
• Give a brief, concise description of what you want to do
• Include any relevant source code (which you did; nice job)
• Include any errors
• Explain what you want the result to be (which you did; nice job)
• Follow up and mark an answer. For a lot of people, if you don't give credit for a correct answer, they won't help you. Just a tip.

Since you didn't do all of the above, I'm left guessing, so I'm making some assumptions based on your code. You seem to need an EXCEPT statement. The following code was developed on PostgreSQL 9.1.

create temp table my_value(seq_id int);

insert into my_value(seq_id) values
(1), (2), (4), (5), (6), (7);

select unnest(array[1, 2, 3, 4, 5, 6, 7, 8, 9]) 
EXCEPT 
select distinct seq_id from my_value;

I'm assuming that you are have a hard coded list of ints (like in your example in the question). I just created a temp table for testing and demo purposes, but I'm sure you can make the necessary adjustments to work in your situation. If you don't have a hard-coded list of ints, then you just need to do a select again whatever source would contain it.

Hope this helps. Welcome to Stackoverflow.

Answer 2

with idlist (id) as (
   values (1),(2),(3),(4),(5),(6),(7),(8),(9)
)
select l.id as missing_seq_id
from idlist l
  left join flu f on f.seqID = l.id
where f.seqID is null;