Re: Odd behavior with NULL value

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
'---''(_/--' `-'\_)