Re: string || NULL ambiguity

On Thu, 20 Mar 2003, Thomas T. Thai wrote:

> > 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)
>
> ---
>
> Note the error: "ERROR:  Void value" above.  Why is that happening?
>

That's because NULL is forbidden for txtidx. We'll fix that.

> --
> Thomas
>
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83