Re: Odd behavior with NULL value

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(at)h-e(dot)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(at)h-e(dot)com
> '---''(_/--' `-'\_)
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>