Re: bug or feature, || -operator and NULLs

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