How to force Postgresql to return None for missing data with ANY SQL statement?

Question

I have a table, let's say:

id | value
1  |  'a'
2  |  'b'

Where for some ID's value might be missing. If I use iterative solution, for example:

for item in my_array:
   data = cursor.execute("""SELECT id from table WHERE value = 'a'""")

If there is no 'a' in DB I get None and can handle it, so for each item I have at least None. But what if I want to do all at once, like:

"""SELECT id from table WHERE value = ANY(array['a', 'b', 'c', 'd'])"""

So I need 4 responses (including None for missing data), while it gives me response only for present data

Gordon Linoff · Accepted Answer · 2016-12-17 13:54:46Z

2

In SQL, you need to generate the data and use left join. In Postgres, you can do:

select v.value, t.id
from (values ('a'), ('b'), ('c'), ('d')) v(value) left join
     table t
     on t.value = v.value;

answered Dec 17, 2016 at 13:54

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vamsi Prabhala Over a year ago

a cross join on distinct id's is needed before a left join.

Maurice Meyer · Accepted Answer · 2016-12-17 14:20:20Z

1

You need to join the table with itself:

select t.id as orig_id, filtered.id as filtered_id
from <table> t
left join(
    select * from <table> where value = ANY(ARRAY[29, 30, 31, 32])
) filtered
on t.id = filtered.id
order by t.id

results in:

orig_id |filtered_id |
--------|------------|
...
22      |            |
27      |            |
28      |            |
29      |29          |
30      |30          |

answered Dec 17, 2016 at 14:20

Maurice Meyer

18.2k4 gold badges35 silver badges54 bronze badges

Collectives™ on Stack Overflow

How to force Postgresql to return None for missing data with ANY SQL statement?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related