Thread: Re: [SQL] how can tell if a column is a primary key?
Hello All, I'm trying to find out if PostgreSQL has a function like NVL that replaces null values with a default? I have a table with datetime fields, some of which are null. I'm trying to extract the date and time components with thedate() and time() functions but the null values break these functions. If NVL is not there, could anyone suggest alternatives? TIA, Gary MacMinn ================ Table = test1 +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | t1 | text | var | | d1 | datetime | 8 | +----------------------------------+----------------------------------+-------+ Query : SELECT t1, date(d1) from test1; //The date() function breaks if the d1 field is not set.
"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 withCOALESCE(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
> > 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
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
> > 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