Search code examples
sqlpostgresqljoin

select elements that does not exist in database but exists in given postgres array

I want to select elements that are not present in the database, but exist in a postgres array. To visualize:

enter image description here

Assume that I have this data in my table:

enter image description here

I have come close after some research, but I do not get exactly what I expected. My current query is:

WITH res AS (SELECT entity_number FROM entity_coordinates WHERE entity_number IN (
    'MG1735401016/6',
  'NON-EXIST-1',
  'P171025002876-1',
  'P170321400780-1',
  'NON-EXIST-2'
))

SELECT *
FROM unnest(ARRAY[
    'MG1735401016/6',
  'NON-EXIST-1',
  'P171025002876-1',
  'P170321400780-1',
  'NON-EXIST-2'
]) item_id
FULL OUTER JOIN res ON entity_number=item_id

My expected result is:

enter image description here

I tried different join types but no luck.

Any help is appreciated

Solution

  • So, this query should achieve what you're trying to do without any JOINs (NOT EXISTS is the most straight forward way to check if any entity_number corrsponds with str (element of array)):

    SELECT str
FROM unnest(ARRAY[
          'MG1735401016/6',
          'NON-EXIST-1',
          'P171025002876-1',
          'P170321400780-1',
          'NON-EXIST-2'
        ]) AS str
WHERE NOT EXISTS (
    SELECT 1
    FROM entity_coordinates
    WHERE entity_number = str
);