<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Conditional Expressions</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="PREVIOUS" TITLE="Sequence Manipulation Functions" HREF="functions-sequence.html"><LINK REL="NEXT" TITLE="Array Functions and Operators" HREF="functions-array.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions-sequence.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 9. Functions and Operators</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions-array.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-CONDITIONAL" >9.13. Conditional Expressions</A ></H1 ><A NAME="AEN11999" ></A ><A NAME="AEN12001" ></A ><P > This section describes the <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM >-compliant conditional expressions available in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >. </P ><DIV CLASS="TIP" ><BLOCKQUOTE CLASS="TIP" ><P ><B >Tip: </B > If your needs go beyond the capabilities of these conditional expressions you might want to consider writing a stored procedure in a more expressive programming language. </P ></BLOCKQUOTE ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN12008" >9.13.1. <TT CLASS="LITERAL" >CASE</TT ></A ></H2 ><P > The <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > <TT CLASS="TOKEN" >CASE</TT > expression is a generic conditional expression, similar to if/else statements in other languages: </P><PRE CLASS="SYNOPSIS" >CASE WHEN <TT CLASS="REPLACEABLE" ><I >condition</I ></TT > THEN <TT CLASS="REPLACEABLE" ><I >result</I ></TT > [<SPAN CLASS="OPTIONAL" >WHEN ...</SPAN >] [<SPAN CLASS="OPTIONAL" >ELSE <TT CLASS="REPLACEABLE" ><I >result</I ></TT ></SPAN >] END</PRE ><P> <TT CLASS="TOKEN" >CASE</TT > clauses can be used wherever an expression is valid. <TT CLASS="REPLACEABLE" ><I >condition</I ></TT > is an expression that returns a <TT CLASS="TYPE" >boolean</TT > result. If the result is true then the value of the <TT CLASS="TOKEN" >CASE</TT > expression is the <TT CLASS="REPLACEABLE" ><I >result</I ></TT > that follows the condition. If the result is false any subsequent <TT CLASS="TOKEN" >WHEN</TT > clauses are searched in the same manner. If no <TT CLASS="TOKEN" >WHEN</TT > <TT CLASS="REPLACEABLE" ><I >condition</I ></TT > is true then the value of the case expression is the <TT CLASS="REPLACEABLE" ><I >result</I ></TT > in the <TT CLASS="TOKEN" >ELSE</TT > clause. If the <TT CLASS="TOKEN" >ELSE</TT > clause is omitted and no condition matches, the result is null. </P ><P > An example: </P><PRE CLASS="SCREEN" >SELECT * FROM test; a --- 1 2 3 SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other</PRE ><P> </P ><P > The data types of all the <TT CLASS="REPLACEABLE" ><I >result</I ></TT > expressions must be convertible to a single output type. See <A HREF="typeconv-union-case.html" >Section 10.5</A > for more detail. </P ><P > The following <SPAN CLASS="QUOTE" >"simple"</SPAN > <TT CLASS="TOKEN" >CASE</TT > expression is a specialized variant of the general form above: </P><PRE CLASS="SYNOPSIS" >CASE <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > WHEN <TT CLASS="REPLACEABLE" ><I >value</I ></TT > THEN <TT CLASS="REPLACEABLE" ><I >result</I ></TT > [<SPAN CLASS="OPTIONAL" >WHEN ...</SPAN >] [<SPAN CLASS="OPTIONAL" >ELSE <TT CLASS="REPLACEABLE" ><I >result</I ></TT ></SPAN >] END</PRE ><P> The <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > is computed and compared to all the <TT CLASS="REPLACEABLE" ><I >value</I ></TT > specifications in the <TT CLASS="TOKEN" >WHEN</TT > clauses until one is found that is equal. If no match is found, the <TT CLASS="REPLACEABLE" ><I >result</I ></TT > in the <TT CLASS="TOKEN" >ELSE</TT > clause (or a null value) is returned. This is similar to the <CODE CLASS="FUNCTION" >switch</CODE > statement in C. </P ><P > The example above can be written using the simple <TT CLASS="TOKEN" >CASE</TT > syntax: </P><PRE CLASS="SCREEN" >SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other</PRE ><P> </P ><P > A <TT CLASS="TOKEN" >CASE</TT > expression does not evaluate any subexpressions that are not needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure: </P><PRE CLASS="PROGRAMLISTING" >SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;</PRE ><P> </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN12058" >9.13.2. <TT CLASS="LITERAL" >COALESCE</TT ></A ></H2 ><A NAME="AEN12061" ></A ><PRE CLASS="SYNOPSIS" ><CODE CLASS="FUNCTION" >COALESCE</CODE >(<TT CLASS="REPLACEABLE" ><I >value</I ></TT > [<SPAN CLASS="OPTIONAL" >, ...</SPAN >])</PRE ><P > The <CODE CLASS="FUNCTION" >COALESCE</CODE > 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: </P><PRE CLASS="PROGRAMLISTING" >SELECT COALESCE(description, short_description, '(none)') ...</PRE ><P> </P ><P > Like a <TT CLASS="TOKEN" >CASE</TT > expression, <CODE CLASS="FUNCTION" >COALESCE</CODE > 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. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN12073" >9.13.3. <TT CLASS="LITERAL" >NULLIF</TT ></A ></H2 ><A NAME="AEN12076" ></A ><PRE CLASS="SYNOPSIS" ><CODE CLASS="FUNCTION" >NULLIF</CODE >(<TT CLASS="REPLACEABLE" ><I >value1</I ></TT >, <TT CLASS="REPLACEABLE" ><I >value2</I ></TT >)</PRE ><P > The <CODE CLASS="FUNCTION" >NULLIF</CODE > function returns a null value if and only if <TT CLASS="REPLACEABLE" ><I >value1</I ></TT > and <TT CLASS="REPLACEABLE" ><I >value2</I ></TT > are equal. Otherwise it returns <TT CLASS="REPLACEABLE" ><I >value1</I ></TT >. This can be used to perform the inverse operation of the <CODE CLASS="FUNCTION" >COALESCE</CODE > example given above: </P><PRE CLASS="PROGRAMLISTING" >SELECT NULLIF(value, '(none)') ...</PRE ><P> </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="functions-sequence.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="functions-array.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Sequence Manipulation Functions</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Array Functions and Operators</TD ></TR ></TABLE ></DIV ></BODY ></HTML >