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

Postgresql – How to return null if id does not exist

For the example table acts below:

id request_id
1 234
2 531

and the query below:

select request_id, id
from acts
where id in (234,531,876)

I need to get the following result:

request_id id
234 1
531 2
876 null

As you could see there is not row where request_id 876 exists. For these cases null should be returned.

How could I achieve this?

Tags:

2

Answers


  1. 0

    Conditional logic can be helpful in this situation.

    for (Object item : database) {
        if (item.getId() == id) {
            return item;
        }
    }
    return null;
    Login or Signup to reply.
  2. 0

    Assuming you actually meant to join on request_id not id.

    create table acts (id integer, request_id integer);

insert into acts values (1, 234), (2, 531);

select 
   * 
from 
   (values(234), (531), (876)) as t(r_id)  
left join 
    acts on t.r_id  = acts.request_id;

r_id |  id  | request_id 
------+------+------------
  234 |    1 |        234
  531 |    2 |        531
  876 | NULL |       NULL

    The above creates a VALUES list that you can then LEFT JOIN to acts. In that case the request_id missing in acts will show up with NULL values for the corresponding field in acts.

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

Back To Top
Search