1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>9.13.Conditional Expressions</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="functions.html" title="Chapter9.Functions and Operators">
<link rel="prev" href="functions-sequence.html" title="9.12.Sequence Manipulation Functions">
<link rel="next" href="functions-array.html" title="9.14.Array Functions and Operators">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="functions-conditional"></a>9.13.Conditional Expressions</h2></div></div></div>
<a name="id617654"></a><a name="id617661"></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" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Tip</h3>
<p> 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>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id617688"></a>9.13.1.<code class="literal">CASE</code></h3></div></div></div>
<p> The <acronym class="acronym">SQL</acronym> <code class="token">CASE</code> expression is a
generic conditional expression, similar to if/else statements in
other languages:
</p>
<pre class="synopsis">CASE WHEN <em class="replaceable"><code>condition</code></em> THEN <em class="replaceable"><code>result</code></em>
[<span class="optional">WHEN ...</span>]
[<span class="optional">ELSE <em class="replaceable"><code>result</code></em></span>]
END</pre>
<p>
<code class="token">CASE</code> clauses can be used wherever
an expression is valid. <em class="replaceable"><code>condition</code></em> is an
expression that returns a <code class="type">boolean</code> result. If the result is true
then the value of the <code class="token">CASE</code> expression is the
<em class="replaceable"><code>result</code></em> that follows the condition. If the result is false any
subsequent <code class="token">WHEN</code> clauses are searched in the same
manner. If no <code class="token">WHEN</code>
<em class="replaceable"><code>condition</code></em> is true then the value of the
case expression is the <em class="replaceable"><code>result</code></em> in the
<code class="token">ELSE</code> clause. If the <code class="token">ELSE</code> 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 <em class="replaceable"><code>result</code></em>
expressions must be convertible to a single output type.
See <a href="typeconv-union-case.html" title="10.5.UNION, CASE, and Related Constructs">Section10.5, “<code class="literal">UNION</code>, <code class="literal">CASE</code>, and Related Constructs”</a> for more detail.
</p>
<p> The following “<span class="quote">simple</span>” <code class="token">CASE</code> expression is a
specialized variant of the general form above:
</p>
<pre class="synopsis">CASE <em class="replaceable"><code>expression</code></em>
WHEN <em class="replaceable"><code>value</code></em> THEN <em class="replaceable"><code>result</code></em>
[<span class="optional">WHEN ...</span>]
[<span class="optional">ELSE <em class="replaceable"><code>result</code></em></span>]
END</pre>
<p>
The
<em class="replaceable"><code>expression</code></em> is computed and compared to
all the <em class="replaceable"><code>value</code></em> specifications in the
<code class="token">WHEN</code> clauses until one is found that is equal. If
no match is found, the <em class="replaceable"><code>result</code></em> in the
<code class="token">ELSE</code> 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
<code class="token">CASE</code> 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 <code class="token">CASE</code> 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" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id617923"></a>9.13.2.<code class="literal">COALESCE</code></h3></div></div></div>
<a name="id617930"></a><a name="id617937"></a><a name="id617943"></a><pre class="synopsis"><code class="function">COALESCE</code>(<em class="replaceable"><code>value</code></em> [<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. It is often used 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 <code class="token">CASE</code> 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. This SQL-standard function provides capabilities similar
to <code class="function">NVL</code> and <code class="function">IFNULL</code>, which are used in some other
database systems.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id618019"></a>9.13.3.<code class="literal">NULLIF</code></h3></div></div></div>
<a name="id618026"></a><pre class="synopsis"><code class="function">NULLIF</code>(<em class="replaceable"><code>value1</code></em>, <em class="replaceable"><code>value2</code></em>)</pre>
<p> The <code class="function">NULLIF</code> function returns a null value if
<em class="replaceable"><code>value1</code></em> and <em class="replaceable"><code>value2</code></em>
are equal; otherwise it returns <em class="replaceable"><code>value1</code></em>.
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>
<p> If <em class="replaceable"><code>value1</code></em> is <code class="literal">(none)</code>, return a null,
otherwise return <em class="replaceable"><code>value1</code></em>.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id618106"></a>9.13.4.<code class="literal">GREATEST</code> and <code class="literal">LEAST</code></h3></div></div></div>
<a name="id618120"></a><a name="id618126"></a><pre class="synopsis"><code class="function">GREATEST</code>(<em class="replaceable"><code>value</code></em> [<span class="optional">, ...</span>])</pre>
<pre class="synopsis"><code class="function">LEAST</code>(<em class="replaceable"><code>value</code></em> [<span class="optional">, ...</span>])</pre>
<p> The <code class="function">GREATEST</code> and <code class="function">LEAST</code> functions select the
largest or smallest value from a list of any number of expressions.
The expressions must all be convertible to a common data type, which
will be the type of the result
(see <a href="typeconv-union-case.html" title="10.5.UNION, CASE, and Related Constructs">Section10.5, “<code class="literal">UNION</code>, <code class="literal">CASE</code>, and Related Constructs”</a> for details). NULL values
in the list are ignored. The result will be NULL only if all the
expressions evaluate to NULL.
</p>
<p> Note that <code class="function">GREATEST</code> and <code class="function">LEAST</code> are not in
the SQL standard, but are a common extension.
</p>
</div>
</div></body>
</html>
|
