From: | Andrew Perrin <aperrin(at)socrates(dot)berkeley(dot)edu> |
---|---|
To: | "Ross J(dot) Reedstrom" <reedstrm(at)rice(dot)edu> |
Cc: | Andrew Perrin <aperrin(at)socrates(dot)Berkeley(dot)EDU>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: PL/PgSQL and NULL |
Date: | 2001-03-11 23:57:04 |
Message-ID: | Pine.LNX.4.21.0103111856070.6221-100000@nujoma.perrins |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Thanks - I'll work on it that way. I know the general-case min() should
probably return NULL if any element is null, but I'm in need of what I
described for a specific case in which the result should be "the minimum
non-null entry", which of course is NULL if all entries are null.
----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
aperrin(at)socrates(dot)berkeley(dot)edu - aperrin(at)igc(dot)apc(dot)org
On Sun, 11 Mar 2001, Ross J. Reedstrom wrote:
>
> On Sun, Mar 11, 2001 at 10:38:10PM +0100, Peter Eisentraut wrote:
> > Andrew Perrin writes:
> >
> > > I'm trying to write what should be a simple function that returns the
> > > minimim of two integers. The complication is that when one of the two
> > > integers is NULL, it should return the other; and when both are NULL, it
> > > should return NULL.
> >
> > Functions involving NULLs don't work well before version 7.1.
> >
>
> True but a little terse, aren't we Peter? Functions all return null if
> any of their parameters are null, prior to v 7.1, as Peter pointed out.
> In 7.1, they only behave this way if marked 'strict'.
>
> Arguably, that's the _right_ behavior for the case your describing:
> in tri-valued logic, NULL means UNKNOWN: it could be any value. So
> min(x,NULL) is UNKNOWN for any value of x, since the NULL could be larger
> or smaller. If you want to do it anyway, you'll have to code your logic
> directly in the SQL query. You'll find the COALESCE function useful:
> it returns the first non-NULL argument. Combined with CASE, you should
> be able to do return the minimum, non-null entry.
>
> Exact code left as an excercise for the reader. ;-)
>
> Ross
>
From | Date | Subject | |
---|---|---|---|
Next Message | datactrl | 2001-03-12 01:18:52 | psql win32 version |
Previous Message | Ross J. Reedstrom | 2001-03-11 22:19:20 | Re: PL/PgSQL and NULL |