Re: use null or 0 in foreign key column, to mean "no value"?
От | Jan de Visser |
---|---|
Тема | Re: use null or 0 in foreign key column, to mean "no value"? |
Дата | |
Msg-id | 2600101.Y9m2JbqpjT@bison обсуждение исходный текст |
Ответ на | use null or 0 in foreign key column, to mean "no value"? (Robert Nikander <rob.nikander@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: