Re: string || NULL ambiguity

>> On Wed, 2003-03-19 at 20:32, Oleg Bartunov wrote:
>>> On Wed, 19 Mar 2003, Alvaro Herrera wrote:
>>> > stringA||COALESCE(NULL, '')
>>> >
>>>
>>> we don't know in advance if it's NULL or not.
>>
>> Right, that's the point of COALESCE: the first non-NULL argument is
>> returned -- so if the first argument to COALESCE happens to be
>> non-NULL, COALESCE has no effect.
>
> I tried this with a txtidx column type:
>
> tsearch=# select coalesce(NULL,'');
> case
> ------
>
> (1 row)
>
> tsearch=# select coalesce(NULL,'hi');
> case
> ------
> hi
> (1 row)
>
> tsearch=# select title_fts from article;
> title_fts
> ----------------------------------
> '2':3A 'tsearch':1A 'version':2A
> 'easi':1A 'implement':2A
>
> (3 rows)
>
> tsearch=# select coalesce('',title_fts) from article;
> ERROR: Void value
> tsearch=# select coalesce('hi',title_fts) from article;
> case
> ------
> 'hi'
> 'hi'
> 'hi'
> (3 rows)
>
> ---

Sorry the above should have been:

tsearch=# select coalesce(title_fts, '') from article;
ERROR: Void value
tsearch=# select coalesce(title_fts, 'hi') from article;
case
----------------------------------
'2':3A 'tsearch':1A 'version':2A
'easi':1A 'implement':2A
'hi'
(3 rows)

> Note the error: "ERROR: Void value" above. Why is that happening?
>
> --
> Thomas
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org