Re: NVL vs COALESCE

[Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>]

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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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>