pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] nulls and datetime
5+ messages / 3 participants
[nested] [flat]
* Re: [SQL] nulls and datetime
@ 1999-11-15 06:10 Tom Lane <tgl@sss.pgh.pa.us>
1999-11-15 15:29 ` Re: [SQL] nulls and datetime Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 1999-11-15 06:10 UTC (permalink / raw)
To: Gary MacMinn <G.MacMinn@nca.gov.au>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; pgsql-sql
"Gary MacMinn" <G.MacMinn@nca.gov.au> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] nulls and datetime
1999-11-15 06:10 Re: [SQL] nulls and datetime Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-11-15 15:29 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-11-15 15:41 ` Re: [SQL] nulls and datetime Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Thomas Lockhart @ 1999-11-15 15:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gary MacMinn <G.MacMinn@nca.gov.au>; pgsql-sql
> > 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.
> regression=> SELECT t1, date(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?
No, other than this is the first time I've ever seen that code execute
;)
In previous versions, single-argument functions with NULL input were
short-circuited in fmgr (??) and never actually were called. I put in
this code just as a guard.
Seems to me that NULL in should produce NULL out, which was the old
behavior. I'll add this to my ToDo, unless someone else has already
picked it up.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] nulls and datetime
1999-11-15 06:10 Re: [SQL] nulls and datetime Tom Lane <tgl@sss.pgh.pa.us>
1999-11-15 15:29 ` Re: [SQL] nulls and datetime Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-11-15 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-11-15 16:11 ` Re: [SQL] nulls and datetime Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 1999-11-15 15:41 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Gary MacMinn <G.MacMinn@nca.gov.au>; pgsql-sql
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> In previous versions, single-argument functions with NULL input were
> short-circuited in fmgr (??) and never actually were called.
As long as I've been paying attention (which is only since 6.4 or so),
execQual+fmgr will call the function regardless of the NULL status of
the inputs. But the NULL flag for the output is taken to be the OR of
the input NULL flags --- so you can't return anything but NULL for a
NULL input, yet the function code is run anyway.
(Actually, a single-argument function is passed the isNull pointer as
an undocumented second argument, so it's possible for such a function
to clear isNull and thus return non-NULL for NULL input. ISNULL and
ISNOTNULL work that way, but I haven't seen anything else that uses it.)
This is an incredibly ugly set of kluges, of course, and I'm hoping
to get it cleaned up for 7.0. See my past memos to pg-hackers on fmgr
redesign.
> Seems to me that NULL in should produce NULL out, which was the old
> behavior. I'll add this to my ToDo, unless someone else has already
> picked it up.
If you don't fix it sooner, I will change it when I pass through that
code doing the fmgr interface changes...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] nulls and datetime
1999-11-15 06:10 Re: [SQL] nulls and datetime Tom Lane <tgl@sss.pgh.pa.us>
1999-11-15 15:29 ` Re: [SQL] nulls and datetime Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-11-15 15:41 ` Re: [SQL] nulls and datetime Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-11-15 16:11 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Lockhart @ 1999-11-15 16:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gary MacMinn <G.MacMinn@nca.gov.au>; pgsql-sql
> > Seems to me that NULL in should produce NULL out, which was the old
> > behavior. I'll add this to my ToDo, unless someone else has already
> > picked it up.
> If you don't fix it sooner, I will change it when I pass through that
> code doing the fmgr interface changes...
I started looking at it, and remembered that part of the problem is
that the date type and time type are pass-by-value, so they will need
to use the "undocumented second argument". I'll put it on my ToDo
list, but may not look at it until you fix up fmgr()...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] nulls and datetime
@ 1999-11-17 05:16 Gary MacMinn <G.MacMinn@nca.gov.au>
0 siblings, 0 replies; 5+ messages in thread
From: Gary MacMinn @ 1999-11-17 05:16 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: lockhart@alumni.caltech.edu; pgsql-sql
Tom,
Thanks for that. Coalesce worked like a ripper. It even forced me to update the database to 6.5.2. I'd been putting it off for a while.
Gary
>>> Tom Lane <tgl@sss.pgh.pa.us> 15/11/99 16:10:51 >>>
"Gary MacMinn" <G.MacMinn@nca.gov.au> 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
************
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-11-17 05:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-15 06:10 Re: [SQL] nulls and datetime Tom Lane <tgl@sss.pgh.pa.us>
1999-11-15 15:29 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-11-15 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-11-15 16:11 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-11-17 05:16 Re: [SQL] nulls and datetime Gary MacMinn <G.MacMinn@nca.gov.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox