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

Поиск
Список
Период
Сортировка
От Jeff Davis
Тема Re: bug or feature, || -operator and NULLs
Дата
Msg-id 1161197526.31645.242.camel@dogma.v10.wvs
обсуждение исходный текст
Ответ на Re: bug or feature, || -operator and NULLs  (Andreas Joseph Krogh <andreak@officenet.no>)
Список pgsql-hackers
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




В списке pgsql-hackers по дате отправления:

Предыдущее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: bug or feature, || -operator and NULLs
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: [GENERAL] query log corrupted-looking entries