Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA41276 for ; Mon, 15 Nov 1999 01:12:02 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id BAA15253; Mon, 15 Nov 1999 01:10:51 -0500 (EST) To: "Gary MacMinn" cc: Thomas Lockhart , pgsql-sql@postgreSQL.org Subject: Re: [SQL] nulls and datetime In-reply-to: Your message of Mon, 15 Nov 1999 16:28:19 +1100 Date: Mon, 15 Nov 1999 01:10:51 -0500 Message-ID: <15251.942646251@sss.pgh.pa.us> From: Tom Lane "Gary MacMinn" writes: > I'm trying to find out if PostgreSQL has a function like NVL that > replaces null values with a default? NVL is not in the SQL standard. But COALESCE is, and you can get what you want with COALESCE(x, value-to-use-if-x-is-null) I'd recommend updating to 6.5.2 or later before doing much with COALESCE --- it depends on CASE which was fairly buggy in older releases. > I have a table with datetime fields, some of which are null. I'm > trying to extract the date and time components with the date() and > time() functions but the null values break these functions. Hmmm ... regression=> SELECT t1, date(d1) from test1; ERROR: Unable to convert null datetime to date regression=> SELECT t1, time(d1) from test1; ERROR: Unable to convert null datetime to date This is undesirable, I agree. It seems to me that those routines should just return NULL given NULL input, rather than going out of their way to break applications. Thomas, can you defend this behavior? regards, tom lane