NVL vs COALESCE
When we're having an alias discussion, I'd really like to see NVL in
postgres. Not because of porting from oracle as much as just spelling
that without the reference manual is completely impossible.
Best regards,
Marcus
On Nov 24, 2005, at 21:21 , Marcus Engene wrote:
When we're having an alias discussion, I'd really like to see NVL
in postgres. Not because of porting from oracle as much as just
spelling that without the reference manual is completely impossible.
NVL: what a very unfortunate spelling. (NULL VaLue? NULL Valued
Logic? Named Very Loosely? Someone help me here :) ) AFAICT, COALESCE
is SQL standard, while NVL isn't. I think an index entry might be a
good idea.
Michael Glaesemann
grzm myrealbox com
If we're going to do that we should add IFNULL() from MySQL as well...
Chris
Michael Glaesemann wrote:
Show quoted text
On Nov 24, 2005, at 21:21 , Marcus Engene wrote:
When we're having an alias discussion, I'd really like to see NVL in
postgres. Not because of porting from oracle as much as just spelling
that without the reference manual is completely impossible.NVL: what a very unfortunate spelling. (NULL VaLue? NULL Valued Logic?
Named Very Loosely? Someone help me here :) ) AFAICT, COALESCE is SQL
standard, while NVL isn't. I think an index entry might be a good idea.Michael Glaesemann
grzm myrealbox com---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
When we're having an alias discussion, I'd really like to see NVL in
postgres. Not because of porting from oracle as much as just spelling that
without the reference manual is completely impossible.Best regards,
Marcus
You can found NVL in orafunc on pgfoundry.
Regards
Pavel Stehule
_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/
Michael Glaesemann wrote:
On Nov 24, 2005, at 21:21 , Marcus Engene wrote:
When we're having an alias discussion, I'd really like to see NVL
in postgres. Not because of porting from oracle as much as just
spelling that without the reference manual is completely impossible.NVL: what a very unfortunate spelling. (NULL VaLue? NULL Valued
Logic? Named Very Loosely? Someone help me here :) ) AFAICT, COALESCE
is SQL standard, while NVL isn't. I think an index entry might be a
good idea.
Agreed, documentation patch applied to HEAD and 8.1.X.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Attachments:
/rtmp/difftext/plainDownload
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.295
diff -c -c -r1.295 func.sgml
*** doc/src/sgml/func.sgml 19 Nov 2005 19:44:54 -0000 1.295
--- doc/src/sgml/func.sgml 28 Nov 2005 23:17:42 -0000
***************
*** 7227,7232 ****
--- 7227,7240 ----
<primary>COALESCE</primary>
</indexterm>
+ <indexterm>
+ <primary>NVL</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>IFNULL</primary>
+ </indexterm>
+
<synopsis>
<function>COALESCE</function>(<replaceable>value</replaceable> <optional>, ...</optional>)
</synopsis>
***************
*** 7234,7242 ****
<para>
The <function>COALESCE</function> function returns the first of its
arguments that is not null. Null is returned only if all arguments
! are null. This is often useful to substitute a
! default value for null values when data is retrieved for display,
! for example:
<programlisting>
SELECT COALESCE(description, short_description, '(none)') ...
</programlisting>
--- 7242,7249 ----
<para>
The <function>COALESCE</function> function returns the first of its
arguments that is not null. Null is returned only if all arguments
! are null. It is often used to substitute a default value for
! null values when data is retrieved for display, for example:
<programlisting>
SELECT COALESCE(description, short_description, '(none)') ...
</programlisting>
***************
*** 7246,7252 ****
Like a <token>CASE</token> expression, <function>COALESCE</function> will
not evaluate arguments that are not needed to determine the result;
that is, arguments to the right of the first non-null argument are
! not evaluated.
</para>
</sect2>
--- 7253,7261 ----
Like a <token>CASE</token> expression, <function>COALESCE</function> will
not evaluate arguments that are not needed to determine the result;
that is, arguments to the right of the first non-null argument are
! not evaluated. This SQL-standard function provides capabilities similar
! to <function>NVL</> and <function>IFNULL</>, which are used in some other
! database systems.
</para>
</sect2>
***************
*** 7262,7277 ****
</synopsis>
<para>
! The <function>NULLIF</function> function returns a null value if and only
! if <replaceable>value1</replaceable> and
! <replaceable>value2</replaceable> are equal. Otherwise it returns
! <replaceable>value1</replaceable>. This can be used to perform the
! inverse operation of the <function>COALESCE</function> example
! given above:
<programlisting>
SELECT NULLIF(value, '(none)') ...
</programlisting>
</para>
</sect2>
--- 7271,7289 ----
</synopsis>
<para>
! The <function>NULLIF</function> function returns a null value if
! <replaceable>value1</replaceable> and <replaceable>value2</replaceable>
! are equal; otherwise it returns <replaceable>value1</replaceable>.
! This can be used to perform the inverse operation of the
! <function>COALESCE</function> example given above:
<programlisting>
SELECT NULLIF(value, '(none)') ...
</programlisting>
</para>
+ <para>
+ If <replaceable>value1</replaceable> is <literal>(none)</>, return a null,
+ otherwise return <replaceable>value1</replaceable>.
+ </para>
</sect2>