On Wed, 2006-10-18 at 14:28 +0200, Andreas Joseph Krogh wrote:
> On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
> > > The following query returns NULL in PG:
> > > SELECT NULL || 'fisk';
> > >
> > > But in Oracle, it returns 'fisk':
> > > SELECT NULL || 'fisk' FROM DUAL;
> > >
> > > The latter seems more logical...
> >
> > Why would it be more logical ?
>
> How many times do you *really* want to get the "not known" answer here
> instead
> of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?
>
When you pass the result to an aggregate function. Example:
=> create table test(days int);
CREATE TABLE
=> insert into test values(1);
INSERT 0 1
=> insert into test values(2);
INSERT 0 1
=> insert into test values(NULL);
INSERT 0 1
=> select sum((days::text||' days')::interval) from test;
sum
--------
3 days
(1 row)
=> select sum((coalesce(days::text,'')||' days')::interval) from test;
ERROR: invalid input syntax for type interval: " days"
The last query represents the "auto-coalescing" behavior you are looking
for. However, it creates an error on a query that is perfectly valid.
Regards,
Jeff Davis
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
