CODEWITHSUNDEEP
pythonsqlpostgresql
wasd
wasd

Reputation: 1572

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

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

Upvotes: 1

Views: 86

Answers (2)

Maurice Meyer
Maurice Meyer

Reputation: 18126

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          |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

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;

Upvotes: 2

Related Questions