I believe the spec says nulls are ignored for min/max. Postgres is as far
as I know behaving according to spec.
But I question the original poster's report of Oracle's behavior. I don't
have 9.2.0.8 to test, but on 9.2.0.7:
SQL> select f1, case when f1 is not null then 'not null' else 'null' end if
from t;
F1 IF
---------- ------------------------
1 not null
2 not null
null
SQL> select max(f1) from t;
MAX(F1)
----------
2
SQL> select version from v$instance;
VERSION
---------------------------------------------------
9.2.0.7.0
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Ben
> Sent: Friday, June 29, 2007 10:18 PM
> To: Tom Lane
> Cc: PostgreSQL General ((EN))
> Subject: Re: [GENERAL] greatest/least semantics different between oracle
> and postgres
>
> On Jun 29, 2007, at 9:15 PM, Tom Lane wrote:
>
> > Hmm ... I fear Oracle's behavior is more correct, because if any
> > argument is null (ie, unknown), then who can say what the greatest or
> > least value is? It's unknown (ie, null). But I suspect our behavior
> > is more useful. Comments?
>
> I agree with you. I don't know what the spec says, but it seems clear
> Oracle is doing the proper thing and Postgres is doing the useful thing.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match