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
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter9.Functions and Operators</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="sql.html" title="PartII.The SQL Language">
<link rel="prev" href="datatype-pseudo.html" title="8.13.Pseudo-Types">
<link rel="next" href="functions-comparison.html" title="9.2.Comparison 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="chapter" lang="en" id="functions">
<div class="titlepage"><div><div><h2 class="title">
<a name="functions"></a>Chapter9.Functions and Operators</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="functions.html#functions-logical">9.1. Logical Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-comparison.html">9.2. Comparison Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-math.html">9.3. Mathematical Functions and Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-string.html">9.4. String Functions and Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-binarystring.html">9.5. Binary String Functions and Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-bitstring.html">9.6. Bit String Functions and Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-matching.html">9.7. Pattern Matching</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="functions-matching.html#functions-like">9.7.1. <code class="function">LIKE</code></a></span></dt>
<dt><span class="sect2"><a href="functions-matching.html#functions-similarto-regexp">9.7.2. <code class="function">SIMILAR TO</code> Regular Expressions</a></span></dt>
<dt><span class="sect2"><a href="functions-matching.html#functions-posix-regexp">9.7.3. <acronym class="acronym">POSIX</acronym> Regular Expressions</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="functions-formatting.html">9.8. Data Type Formatting Functions</a></span></dt>
<dt><span class="sect1"><a href="functions-datetime.html">9.9. Date/Time Functions and Operators</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="functions-datetime.html#functions-datetime-extract">9.9.1. <code class="function">EXTRACT</code>, <code class="function">date_part</code></a></span></dt>
<dt><span class="sect2"><a href="functions-datetime.html#functions-datetime-trunc">9.9.2. <code class="function">date_trunc</code></a></span></dt>
<dt><span class="sect2"><a href="functions-datetime.html#functions-datetime-zoneconvert">9.9.3. <code class="literal">AT TIME ZONE</code></a></span></dt>
<dt><span class="sect2"><a href="functions-datetime.html#functions-datetime-current">9.9.4. Current Date/Time</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="functions-geometry.html">9.10. Geometric Functions and Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-net.html">9.11. Network Address Functions and Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-sequence.html">9.12. Sequence Manipulation Functions</a></span></dt>
<dt><span class="sect1"><a href="functions-conditional.html">9.13. Conditional Expressions</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="functions-conditional.html#id617688">9.13.1. <code class="literal">CASE</code></a></span></dt>
<dt><span class="sect2"><a href="functions-conditional.html#id617923">9.13.2. <code class="literal">COALESCE</code></a></span></dt>
<dt><span class="sect2"><a href="functions-conditional.html#id618019">9.13.3. <code class="literal">NULLIF</code></a></span></dt>
<dt><span class="sect2"><a href="functions-conditional.html#id618106">9.13.4. <code class="literal">GREATEST</code> and <code class="literal">LEAST</code></a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="functions-array.html">9.14. Array Functions and Operators</a></span></dt>
<dt><span class="sect1"><a href="functions-aggregate.html">9.15. Aggregate Functions</a></span></dt>
<dt><span class="sect1"><a href="functions-subquery.html">9.16. Subquery Expressions</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="functions-subquery.html#id619835">9.16.1. <code class="literal">EXISTS</code></a></span></dt>
<dt><span class="sect2"><a href="functions-subquery.html#id619960">9.16.2. <code class="literal">IN</code></a></span></dt>
<dt><span class="sect2"><a href="functions-subquery.html#id620072">9.16.3. <code class="literal">NOT IN</code></a></span></dt>
<dt><span class="sect2"><a href="functions-subquery.html#id620193">9.16.4. <code class="literal">ANY</code>/<code class="literal">SOME</code></a></span></dt>
<dt><span class="sect2"><a href="functions-subquery.html#id620399">9.16.5. <code class="literal">ALL</code></a></span></dt>
<dt><span class="sect2"><a href="functions-subquery.html#id620567">9.16.6. Row-wise Comparison</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="functions-comparisons.html">9.17. Row and Array Comparisons</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="functions-comparisons.html#id620732">9.17.1. <code class="literal">IN</code></a></span></dt>
<dt><span class="sect2"><a href="functions-comparisons.html#id620797">9.17.2. <code class="literal">NOT IN</code></a></span></dt>
<dt><span class="sect2"><a href="functions-comparisons.html#id620892">9.17.3. <code class="literal">ANY</code>/<code class="literal">SOME</code> (array)</a></span></dt>
<dt><span class="sect2"><a href="functions-comparisons.html#id620972">9.17.4. <code class="literal">ALL</code> (array)</a></span></dt>
<dt><span class="sect2"><a href="functions-comparisons.html#id621023">9.17.5. Row-wise Comparison</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="functions-srf.html">9.18. Set Returning Functions</a></span></dt>
<dt><span class="sect1"><a href="functions-info.html">9.19. System Information Functions</a></span></dt>
<dt><span class="sect1"><a href="functions-admin.html">9.20. System Administration Functions</a></span></dt>
</dl>
</div>
<a name="id593888"></a><a name="id593898"></a><p> <span class="productname">PostgreSQL</span> provides a large number of
functions and operators for the built-in data types. Users can also
define their own functions and operators, as described in
<a href="server-programming.html" title="PartV.Server Programming">PartV, “Server Programming”</a>. The
<span class="application">psql</span> commands <code class="command">\df</code> and
<code class="command">\do</code> can be used to show the list of all actually
available functions and operators, respectively.
</p>
<p> If you are concerned about portability then take note that most of
the functions and operators described in this chapter, with the
exception of the most trivial arithmetic and comparison operators
and some explicitly marked functions, are not specified by the
<acronym class="acronym">SQL</acronym> standard. Some of the extended functionality
is present in other <acronym class="acronym">SQL</acronym> database management
systems, and in many cases this functionality is compatible and
consistent between the various implementations. This chapter is also
not exhaustive; additional functions appear in relevant sections of
the manual.
</p>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="functions-logical"></a>9.1.Logical Operators</h2></div></div></div>
<a name="id593977"></a><a name="id593992"></a><p> The usual logical operators are available:
<a name="id594007"></a>
<a name="id594014"></a>
<a name="id594022"></a>
<a name="id594030"></a>
<a name="id594037"></a>
<a name="id594045"></a>
</p>
<table class="simplelist" border="0" summary="Simple list">
<tr><td><code class="literal">AND</code></td></tr>
<tr><td><code class="literal">OR</code></td></tr>
<tr><td><code class="literal">NOT</code></td></tr>
</table>
<p>
<acronym class="acronym">SQL</acronym> uses a three-valued Boolean logic where the null value represents
“<span class="quote">unknown</span>”. Observe the following truth tables:
</p>
<div class="informaltable"><table border="1">
<colgroup>
<col>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th><em class="replaceable"><code>a</code></em></th>
<th><em class="replaceable"><code>b</code></em></th>
<th>
<em class="replaceable"><code>a</code></em> AND <em class="replaceable"><code>b</code></em>
</th>
<th>
<em class="replaceable"><code>a</code></em> OR <em class="replaceable"><code>b</code></em>
</th>
</tr></thead>
<tbody>
<tr>
<td>TRUE</td>
<td>TRUE</td>
<td>TRUE</td>
<td>TRUE</td>
</tr>
<tr>
<td>TRUE</td>
<td>FALSE</td>
<td>FALSE</td>
<td>TRUE</td>
</tr>
<tr>
<td>TRUE</td>
<td>NULL</td>
<td>NULL</td>
<td>TRUE</td>
</tr>
<tr>
<td>FALSE</td>
<td>FALSE</td>
<td>FALSE</td>
<td>FALSE</td>
</tr>
<tr>
<td>FALSE</td>
<td>NULL</td>
<td>FALSE</td>
<td>NULL</td>
</tr>
<tr>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
</tr>
</tbody>
</table></div>
<p>
</p>
<div class="informaltable"><table border="1">
<colgroup>
<col>
<col>
</colgroup>
<thead><tr>
<th><em class="replaceable"><code>a</code></em></th>
<th>NOT <em class="replaceable"><code>a</code></em>
</th>
</tr></thead>
<tbody>
<tr>
<td>TRUE</td>
<td>FALSE</td>
</tr>
<tr>
<td>FALSE</td>
<td>TRUE</td>
</tr>
<tr>
<td>NULL</td>
<td>NULL</td>
</tr>
</tbody>
</table></div>
<p>
</p>
<p> The operators <code class="literal">AND</code> and <code class="literal">OR</code> are
commutative, that is, you can switch the left and right operand
without affecting the result. But see <a href="sql-expressions.html#syntax-express-eval" title="4.2.12.Expression Evaluation Rules">Section4.2.12, “Expression Evaluation Rules”</a> for more information about the
order of evaluation of subexpressions.
</p>
</div>
</div></body>
</html>
|
