Re: Odd behavior with NULL value - Mailing list pgsql-admin

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