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

Postgresql – Return a value from where condition parameter; if it's not exists in the table

I want a query that retrieves the result of values if it doesn’t exist.

Example: select * from the table.A where column1 in (1,2,3)
If 2, 3 values exist then I will get 2 and 3 as a result.

But I need the values which are not available in the table i.e., 1.

Any help is much appreciated!

Tags:

2

Answers


  1. 0

    You can build a list of values like this:

    VALUES (1),(2),(3)

    And excluding one result from another one can be done using EXCEPT. See also the documentation.

    Together, you can do following:

    VALUES (1),(2),(3)
EXCEPT
SELECT column1 FROM yourtable;

    So for example for this sample data:

    column1
    2
    3
    4

    The result of above query will be:

    column1
    1

    See this fiddle example

    Login or Signup to reply.
  2. 0

    Your question is ambiguous, do you want:

    1. Items which do not exist (as others provide)?
    2. Values for existing items and null for items that do not exist?

    If you are seeking the latter then you need to create an object containing the wanted values and then LEFT join the existing table. (See demo)

    with wanted(id) as 
     ( values (1), (2), (3) ) 
select wanted.id wanted
     , tl.<col>
     -- ...
  from wanted
  left join <some_table> t1
         on t1.<some_col> = wanted.id
 order by wanted.id;

    The CTE could be another table instead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search