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

Postgresql – 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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After some research I found another solution that uses EXCEPT:

    SELECT *
FROM unnest(ARRAY[
    'MG1735401016/6',
  'NON-EXIST-1',
  'P171025002876-1',
  'P170321400780-1',
  'NON-EXIST-2'
]) AS t(id)

EXCEPT 

SELECT entity_number FROM entity_coordinates

    But my benchmarks show that @nafrolov's solution is nearly twice as fast, so I accepted his answer


  2. 0

    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
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search