Question 114190 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Query to find Strings that are not in a table

I am trying to write a query that returns all the strings that are not present in a table when given a list of strings.

When I execute something like:

select * 
from public.person 
where person_name in ('John', 'Doe', 'Mike')

it will find the person that has the name in the list of strings given, but I want to know the names that are not in the list.

If we have Mike, John, Ben in public.person, and I want to pass a list containing

'John', 'Doe', 'Mike'

and I want the query to return

'Doe'

as it is not present.

How can I modify the query to return the Strings present in the list I give and that are not contained in the table?

Tags:

2

Answers


  1. 0

    You can create a table or derived tables with the names you are wanting to feed in, then LEFT OUTER JOIN to your table.

    CREATE TABLE persons (name varchar(20));

INSERT INTO persons VALUES ('mike'),('john'),('ben');

WITH inNames AS (SELECT unnest(array['john','doe','mike']) as name)
SELECT inNames.name
FROM inNames 
   LEFT OUTER JOIN persons ON inNames.name = persons.name
WHERE persons.name IS NULL;

    doe

    dbfiddle

    Login or Signup to reply.
  2. 0

    You could probably do something like this:

    SELECT name
FROM   (VALUES('John'),
              ('Doe'),
              ('Mike')) E(name)  
EXCEPT
SELECT person_name
FROM public.person

    Or you might insert the list into a temp table and do a left join (if null):

    SELECT * 
FROM tempTable t
  LEFT public.person p ON p.person_name=t.name
WHERE p.name IS NULL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search