Thanks for setting that out so nicely.
This is not an issue with MS Access (for example), which makes no
distinction between an empty string and a null. In that case the
concatenation with a null produces a non-null result. This makes sense
considering the target user group for MS Access, who generally can be
expected to not care about the distinction between 'empty' and 'null', and
who would be confounded by a null result.
Postgres, being primarily a backend application, needs to be SQL compliant
(as someone else pointed out). The developer can smooth away the problem
for the front end user.
Being myself somewhere between the two camps, I sometimes get confounded
anyway :-)
> -----Original Message-----
> From: Nick Fankhauser [SMTP:nickf@ontko.com]
> Sent: Thursday, November 09, 2000 12:05 PM
> To: Jeff Eckermann; gserrato@sager.telecom-co.net;
> pgsql-general@postgresql.org; Stephan Szabo
> Subject: RE: [GENERAL] Appending null produces null? AND RE:
> [GENERAL] NVL (sql oracle)
>
> Stephan Szabo wrote:
>
> > If I'm not misremembering what NVL does, the equivalent
> > is coalesce.
>
> Sure enough!
>
> ... so the answer to the NVL question is that coalesce does it, and the
> answer to Jeff Eckermann's question goes something like this:
>
> test=> select * from hiho;
> one |two
> ----+------
> hiho|
> hip |hop
> |hophip
> (3 rows)
>
> test=> select one,two,coalesce(one,'')||coalesce(two,'') from hiho;
> one |two |?column?
> ----+------+--------
> hiho| |hiho
> hip |hop |hiphop
> |hophip|hophip
> (3 rows)
>
>
> Thanks Stephan!
>
>
>
> -Nick
>
> ---------------------------------------------------------------------
> Nick Fankhauser
>
> Business:
> nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
> Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
>
> Personal:
> nickf@fankhausers.com http://www.infocom.com/~nickf