Обсуждение: use null or 0 in foreign key column, to mean "no value"?

Hi,

(Maybe my subject line should be: `is not distinct from` and indexes.)

In Postgres 9.4, I’ve got a table of ‘items’ that references a table ‘colors’.  Not all items have colors, so I created
anullable column in items like: 

      color_id bigint references colors

There is also an index on color_id:

      create index on items (color_id);

I thought this was the right way to do it, but now I’m not so sure... In application code, prepared statements want to
say:`select * from items where color_id = ?` and that `?` might be a int or null, so that doesn’t work.  I used `is not
distinctfrom` instead of =, which has the right meaning, but now I notice it doesn’t use the index for queries that
replace`=` with `is not distinct from`, and queries run much slower.  Using `explain` confirms: it’s doing sequential
scanswhere `=` was using index. 

So… is this bad DB design to use null to mean that an item has no color? Should I instead put a special row in
`colors`,maybe with id = 0, to represent the “no color” value?  Or is there some way to make an index work with nulls
and`is not distinct from`?   

thank you,
Rob

On June 26, 2015 11:59:05 PM Robert Nikander wrote:
> Hi,
>
> (Maybe my subject line should be: `is not distinct from` and indexes.)
>
> In Postgres 9.4, I’ve got a table of ‘items’ that references a table
> ‘colors’.  Not all items have colors, so I created a nullable column in
> items like:
>
>       color_id bigint references colors
>
> There is also an index on color_id:
>
>       create index on items (color_id);
>
> I thought this was the right way to do it, but now I’m not so sure... In
> application code, prepared statements want to say: `select * from items
> where color_id = ?` and that `?` might be a int or null, so that doesn’t
> work.  I used `is not distinct from` instead of =, which has the right
> meaning, but now I notice it doesn’t use the index for queries that replace
> `=` with `is not distinct from`, and queries run much slower.  Using
> `explain` confirms: it’s doing sequential scans where `=` was using index.

I test for NULL in my application code and emit '... WHERE foo = ?' if the
value is not NULL and '... WHERE foo IS NOT NULL' otherwise. ISTR that that
actually uses indexes.

>
> So… is this bad DB design to use null to mean that an item has no color?
> Should I instead put a special row in `colors`, maybe with id = 0, to
> represent the “no color” value?  Or is there some way to make an index work
> with nulls and `is not distinct from`?
>
> thank you,
> Rob

So… is this bad DB design to use null to mean that an item has no color? Should I instead put a special row in `colors`, maybe with id = 0, to represent the “no color” value?  Or is there some way to make an index work with nulls and `is not distinct from`?

> On 27 Jun 2015, at 5:59, Robert Nikander <rob.nikander@gmail.com> wrote:
> In application code, prepared statements want to say: `select * from items where color_id = ?` and that `?` might be
aint or null, so that doesn’t work.  

You could add another parameter to test which expression to 'activate' in the query, something like:

select * from items where ('notnull' = ? and color_id = ?) or ('null' = ? and color_id is null);

Of course, with those _positional_ query parameters that means you need to add the same value TWICE into the query. You
wouldn'tneed to with _named_ query parameters, if those are available to you. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.