Re: How to setup default value "0000-00-00" for "date"
От | Michal Taborsky |
---|---|
Тема | Re: How to setup default value "0000-00-00" for "date" |
Дата | |
Msg-id | 4125B8CE.5010801@taborsky.cz обсуждение исходный текст |
Ответ на | Re: How to setup default value "0000-00-00" for "date" (Richard Huxton <dev@archonet.com>) |
Ответы |
Re: How to setup default value "0000-00-00" for "date"
(Karsten Hilbert <Karsten.Hilbert@gmx.net>)
Re: How to setup default value "0000-00-00" for "date" ("Jim Wilson" <jimw@kelcomaine.com>) Re: How to setup default value "0000-00-00" for "date" (Marco Colombo <marco@esi.it>) |
Список | pgsql-general |
Richard Huxton wrote: > Where you don't have a valid date to store you should use NULL. This > business of storing zeroes is a horrible MySQL design mistake. Well, yes and no. It certainly is a design mistake and introduces incosistency into the database, but after I was bitten several times by NULL values I'd go for solution like this any day. Let me explain. We had a table of messages, which was inserted to randomly and every few minutes we'd walk through the unprocessed messages and perform some work on them. I, trying to have the database as clean as possible, used this table definition (simplified): messages ( id serial, data text, arrived timestamp default now, processed timestamp) So after the message arrived, it had the processed field set to null, which was perfectly consistent and represented what it realy was--an unknown value. We'd then simply SELECT * FROM messages WHERE processed IS NULL and were happy ever after, only to realise after the table had grown to few thousands rows, that the SELECT took ages, because the system had to perform seqscan. Aha! So we added an index on processed, because common sense told me, that as long as there are 100k rows and only 10 of them are NULL, the index would be very selective and therefore useful. I guess you know where it ends--the index is not used for IS [NOT] NULL expressions. The obvious workaround was to add DEFAULT value to "processed" in form of kind of anchor (we used '-infinity') and then do SELECT * FROM messages WHERE processed='-infinity'. Bingo! The thing went 100x faster. So we could choose to have standards-compliant, very clean database design OR the thing that does what it's supposed to do in reasonable time. And believe me, it's kind of difficult to explain to our logistics department that we could have done the thing to return results in milliseconds instead of 10 secs, but chose not to for sake of clean design. It'd be really nice if we didn't have to use such hacks, but hey, life's inperfect. And so that this would not be just a literary exercise and to answer Emi's question--you can't do that, but use some valid date which you are never going to use for your ordinary data (like the '-infinity', or 1.1.1970 00:00). Just make sure you make a note of it somewhere and my suggestion is you write a COMMENT ON that column for future generations. -- Michal Taborsky http://www.taborsky.cz
В списке pgsql-general по дате отправления:
Предыдущее
От: Christian KratzerДата:
Сообщение: Re: How to setup default value "0000-00-00" for "date"