postgresql - Is there a way to query for an integer value or NULL without using OR? -

i'd query (list of) values or null not use or. reasoning behind trying not use or is, need use index on field speed query.

a simple example illustrate question:

create table fruits (   name text,   quantity integer ); 

(the real table has lots of additional integer columns.)

the query i'm not happy is

select * fruits quantity in (1,2,3,4) or quantity null; 

the query i'm hoping like

select * fruits quantity magic (1,2,3,4,null); 

i'm using postgresql 9.1.

as far can tell docs (e.g. http://www.postgresql.org/docs/9.1/static/functions-comparisons.html) , tests there no way this. i'm hoping 1 of has magic insight.

test table 100k rows:

create table fruits (name text, quantity integer); insert fruits (name, quantity) select left(md5(i::text), 6), generate_series(1, 10000) s(i); 

with plain index on quantity:

create index fruits_index on fruits(quantity); analyze fruits; 

the query or:

explain analyze select * fruits quantity in (1,2,3,4) or quantity null;                                                          query plan                                                          ----------------------------------------------------------------------------------------------------------------------------  bitmap heap scan on fruits  (cost=21.29..34.12 rows=4 width=11) (actual time=0.032..0.032 rows=4 loops=1)    recheck cond: ((quantity = ('{1,2,3,4}'::integer[])) or (quantity null))    ->  bitmapor  (cost=21.29..21.29 rows=4 width=0) (actual time=0.025..0.025 rows=0 loops=1)          ->  bitmap index scan on fruits_index  (cost=0.00..17.03 rows=4 width=0) (actual time=0.019..0.019 rows=4 loops=1)                index cond: (quantity = ('{1,2,3,4}'::integer[]))          ->  bitmap index scan on fruits_index  (cost=0.00..4.26 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)                index cond: (quantity null)  total runtime: 0.089 ms 

without or:

explain analyze select * fruits quantity in (1,2,3,4);                                                       query plan                                                        -----------------------------------------------------------------------------------------------------------------------  index scan using fruits_index on fruits  (cost=0.00..21.07 rows=4 width=11) (actual time=0.026..0.038 rows=4 loops=1)    index cond: (quantity = ('{1,2,3,4}'::integer[]))  total runtime: 0.085 ms 

the coalesce version proposed wildplasser leads sequential scan:

explain analyze select *  fruits coalesce(quantity, -1) in (-1,1,2,3,4);                                              query plan                                               -----------------------------------------------------------------------------------------------------  seq scan on fruits  (cost=0.00..217.50 rows=250 width=11) (actual time=0.023..4.358 rows=4 loops=1)    filter: (coalesce(quantity, (-1)) = ('{-1,1,2,3,4}'::integer[]))    rows removed filter: 9996  total runtime: 4.395 ms 

unless coalesce expression index created:

create index fruits_coalesce_index on fruits(coalesce(quantity, -1)); analyze fruits;  explain analyze select *  fruits coalesce(quantity, -1) in (-1,1,2,3,4);                                                            query plan                                                            --------------------------------------------------------------------------------------------------------------------------------  index scan using fruits_coalesce_index on fruits  (cost=0.00..25.34 rows=5 width=11) (actual time=0.112..0.124 rows=4 loops=1)    index cond: (coalesce(quantity, (-1)) = ('{-1,1,2,3,4}'::integer[]))  total runtime: 0.172 ms 

but still worse plain or query plain index on quantity.