Skip site navigation (1) Skip section navigation (2)

Re: greatest/least semantics different between oracle and postgres

From: "paul rivers" <rivers(dot)paul(at)gmail(dot)com>
To: "'Pavel Stehule'" <pavel(dot)stehule(at)gmail(dot)com>,"'Bruno Wolff III'" <bruno(at)wolff(dot)to>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: greatest/least semantics different between oracle and postgres
Date: 2007-06-30 18:13:30
Message-ID: 000901c7bb42$5d8436d0$04d09888@parzifal (view raw or flat)
Thread:
Lists: pgsql-general
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Pavel Stehule
> Sent: Saturday, June 30, 2007 10:37 AM
> To: Bruno Wolff III; Pavel Stehule; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] greatest/least semantics different between oracle
> and postgres
> 
> > Maybe that reference was for an earlier version of Oracle and the
> definition
> > changed at some point? I only have access to version 9 and greatest and
> > lest are strict there.
> >
> 
> I am installing OracleXE and I'll test it.
> 
> Pavel
> 

At risk of putting my foot in my mouth again, greatest() returns null if one
or more expressions are null for Oracle enterprise 9.2.0.7 and 10.2.0.3.
 
The docs for greatest() don't talk of NULL:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions060.
htm#SQLRF00645

There are metalink documents that do seem to make it clear greatest/least
are defined to return null if one or more expressions has a null.  (see doc
207279.999 for example)

SQL> select greatest(1,2,null,3) from dual;

GREATEST(1,2,NULL,3)
--------------------


SQL> select greatest(1,2,3) from dual;

GREATEST(1,2,3)
---------------
              3

SQL> select version from v$instance;

VERSION
---------------------------------------------------
9.2.0.7.0




SQL> select greatest(1,2,null,3) from dual;

GREATEST(1,2,NULL,3)
--------------------


SQL> select greatest(1,2,3) from dual;

GREATEST(1,2,3)
---------------
              3

SQL> select version from v$instance;

VERSION
---------------------------------------------------
10.2.0.3.0




In response to

Responses

pgsql-general by date

Next:From: Richard Broersma JrDate: 2007-06-30 18:21:59
Subject: Re: Looking for help regarding getting the latest inserted sequence value.
Previous:From: paul riversDate: 2007-06-30 18:11:18
Subject: Re: greatest/least semantics different between oracle and postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group