use null or 0 in foreign key column, to mean "no value"?
От | Robert Nikander |
---|---|
Тема | use null or 0 in foreign key column, to mean "no value"? |
Дата | |
Msg-id | 3721E9B9-AE49-4C19-BFE2-8578671870BD@gmail.com обсуждение исходный текст |
Ответы |
Re: use null or 0 in foreign key column, to mean "no value"?
Re: use null or 0 in foreign key column, to mean "no value"? Re: use null or 0 in foreign key column, to mean "no value"? Re: use null or 0 in foreign key column, to mean "no value"? |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: