Re: Odd behavior with NULL value
От | Bill Cunningham |
---|---|
Тема | Re: Odd behavior with NULL value |
Дата | |
Msg-id | 3C22C6B8.3020806@ballydev.com обсуждение исходный текст |
Ответ на | Re: Odd behavior with NULL value (bsmith@h-e.com (Bob Smith, Hammett & Edison, Inc.)) |
Список | pgsql-admin |
I apologize if this is sent twice. The simplest solution would be to use a view over the data hiding the mess of the case statement so that ad-hoc users' queries would still work. - Bill Bob Smith, Hammett & Edison, Inc. wrote: >On Thu, Dec 20, 2001, 18:55:18 Tom Lane wrote: > >>bsmith@h-e.com (Bob Smith, Hammett & Edison, Inc.) writes: >> >>>On a related note, does anyone know if 'current' works with DATE? >>> >>DATE does not have an internal representation of 'current', so the DATE >>input parser treats it the same as 'now'. AFAIR only timestamp (nee >>datetime) has that concept. >> >>FYI, the concept of 'current' has been deemed broken and removed >>altogether for 7.2. See discussions in the pgsql-hackers archives >>if you want to know why. >> >> regards, tom lane >> > >Here is the problem I'm trying to solve, perhaps someone can help. For an >invoicing system database, I have a table that defines employees. Each has a >begin and end date defining the employment period. For current employees, the >end date is "open". How do I express that to keep queries as simple as >possible? The three choices I came up with are: > > (1) Use end_date = 'current' > > (2) Store end_date as TEXT and cast it to DATE in expressions, so it can > contain the text constant 'today' for current employees > > (3) Use end_date = NULL and have an extra expression in the queries > >Because 'current' doesn't work for DATE types, (1) is a bust. (2) and (3) >both work, but I'm not sure which is better from a performance point of view. > For example, if I want all the employees that are current as of a particular >date, for (2) it would be: > > SELECT * FROM employee WHERE (foo >= employee.start_date) AND > (foo <= employee.end_date::DATE) > >and for (3) it would be: > > SELECT... WHERE (foo >= employee.start_date) AND > ((foo <= employee.end_date) OR (employee.end_date IS NULL)) > >(Thanks to all who posted with explanations of why (x IS NULL) should be used >instead of (x = NULL)). The cast has a performance penalty, but then so does >using OR, especially in a join. Which would be worse? > >I just noticed that (3) does have one advantage over (2); if the system allows >end_date to be set into the future, (3) works for dates in the future, but (2) >does not. But that isn't one of my requirements so it isn't a deciding >factor. > >Any opinions on which approach is better, or does anyone see a fourth >alternative? > >Thanks! > > > |\ _,,,---,,_ Bob Smith > /,`.-'`' -. ;-;;,_ Hammett & Edison, Inc. > |,4- ) )-,_. ,\ ( `'-' bsmith@h-e.com > '---''(_/--' `-'\_) > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
В списке pgsql-admin по дате отправления: