CODEWITHSUNDEEP
postgresql
Hossam Oukli
Hossam Oukli

Reputation: 1296

Retrieve rows with a column value that do not exist in a given list

Let's say I have a list of values 

'A', 'B', 'C','D','E',...,'X'.

And I have a database column CHARS that is storing the exact values of my list (one value / row) except for 'C' and 'E'. So, CHARS contains 'A', 'B', 'D',..., 'X'.

Is there a way in PostgreSQL to return only the rows for 'C' and 'E'; the values from my list which are missing from column CHARS?

Upvotes: 1

Views: 54

Answers (2)

user330315
user330315

Reputation:

You can do an outer join against a values clause:

select i.ch as missing_character
from ( 
  values 
     ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ..., ('X')
) as i(ch)
  left join the_table t on i.ch = t.chars
where t.chars is null
order by i.ch;

Note that each char needs to be enclosed in parentheses in the values clause to make sure that each one is a single row.

Alternatively you can use the EXCEPT operator:

select ch
from ( 
  values ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ... , ('X') 
) as i(ch)

except 

select chars
from the_table
order by ch

Online example: https://rextester.com/XOUB52627

Upvotes: 1

clemens
clemens

Reputation: 17721

If your list of values come from outside of the database (e.g. a program), the simplest solution should be the NOT INoperator

SELECT * FROM your_table WHERE chars NOT IN ('A', 'B', 'C', 'D', 'E', ..., 'X')

(Note: The missing characters in the tuple can't be abbreviated with .... So you have write them all.)

If your list of values come from a table or query, you can use a subquery:

SELECT * FROM your_table WHERE chars NOT IN (SELECT a_char FROM another_table WHERE ...);

Upvotes: 1

Related Questions