Re: use null or 0 in foreign key column, to mean "no value"? - Mailing list pgsql-general
| From | John McKown |
|---|---|
| Subject | Re: use null or 0 in foreign key column, to mean "no value"? |
| Date | |
| Msg-id | CAAJSdjiOBs2MoSLDZhmatyT11aKGo=YBSh7+kyNwPMje6LuFhg@mail.gmail.com Whole thread Raw |
| In response to | use null or 0 in foreign key column, to mean "no value"? (Robert Nikander <rob.nikander@gmail.com>) |
| List | pgsql-general |
On Fri, Jun 26, 2015 at 10:59 PM, Robert Nikander <rob.nikander@gmail.com> 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.
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`?
_My_ personal opinion is that NULL should _never_ be used to "indicate" anything at all, other than something like "?I don't have that information!" NULL should be anathema because, as you have seen, just complicates coding SQL queries.
What I have read (Joe Celko mainly) is that it is better to have "encoded special values" for "unusual" things. In your case, what does "no color" really mean? Does it mean "it has some color, but I don't know what it is" or is it more like a "transparent" substance, such as pure glass? Or is it "reflective" so that it is seems to have the color of what ever color of light is illuminating it? Maybe a perfect mirror of some sort. Are the "normal" color_id values always non-negative (0 or greater)? If so then I'd encode color_id something like -1 == "some unknown color", -2 == "transparent", -3 == "reflective", or some other meaning. One example that we actually have at work is in an "expiration date" on certain types of data files. Most of the time, this is just a normal date like 2017-01-20. But, sometimes, we want codes to indicate things such as "keep the last 3 copies, and expire previous copies" or "keep until it has not been referenced in 90 days" or "keep forever". We either have to have some "special" date values to indicate these latter non-date case, or we need yet another column for "type of expiration" which would be values for "use date", "permanent", "keep ??? days non-reference", "keep ??? copies" and so forth. We actually don't use an SQL date field, but a text field which we can test and, if necessary, convert to a date or to some "explanatory" text.
thank you,
Rob
Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
My sister opened a computer store in Hawaii. She sells C shells down by the seashore.
My sister opened a computer store in Hawaii. She sells C shells down by the seashore.
If someone tell you that nothing is impossible:
Ask him to dribble a football.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
pgsql-general by date: