Re: Odd behavior with NULL value - Mailing list 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)
>