NVL (sql oracle)

NVL in postgreSQL???

That was also my first thought when looking at Jeff Eckerman's string
concatenation dilemma. I don't find NVL or anything that remotely resembles
it anywhere in the docs. Having such a function would cetainly be handy. Can
anyone clue us in on the process for submitting suggestions to the code
hackers? (or point out a similar function that I missed...)

-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

If I'm not misremembering what NVL does, the equivalent
is coalesce.

Stephan Szabo
sszabo@bigpanda.com

On Thu, 9 Nov 2000, Nick Fankhauser wrote:

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

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 :-)

On Thu, 9 Nov 2000 09:45:53 -0800 (PST), Stephan Szabo wrote:

If I'm not misremembering what NVL does, the equivalent
is coalesce.

or maybe NULLIF?