1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>4.2. Value Expressions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-syntax-lexical.html" title="4.1. Lexical Structure" /><link rel="next" href="sql-syntax-calling-funcs.html" title="4.3. Calling Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">4.2. Value Expressions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-syntax-lexical.html" title="4.1. Lexical Structure">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-syntax.html" title="Chapter 4. SQL Syntax">Up</a></td><th width="60%" align="center">Chapter 4. SQL Syntax</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-syntax-calling-funcs.html" title="4.3. Calling Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="SQL-EXPRESSIONS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">4.2. Value Expressions <a href="#SQL-EXPRESSIONS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="sql-expressions.html#SQL-EXPRESSIONS-COLUMN-REFS">4.2.1. Column References</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-EXPRESSIONS-PARAMETERS-POSITIONAL">4.2.2. Positional Parameters</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-EXPRESSIONS-SUBSCRIPTS">4.2.3. Subscripts</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#FIELD-SELECTION">4.2.4. Field Selection</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-EXPRESSIONS-OPERATOR-CALLS">4.2.5. Operator Invocations</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-EXPRESSIONS-FUNCTION-CALLS">4.2.6. Function Calls</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SYNTAX-AGGREGATES">4.2.7. Aggregate Expressions</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS">4.2.8. Window Function Calls</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-SYNTAX-TYPE-CASTS">4.2.9. Type Casts</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-SYNTAX-COLLATE-EXPRS">4.2.10. Collation Expressions</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES">4.2.11. Scalar Subqueries</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS">4.2.12. Array Constructors</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS">4.2.13. Row Constructors</a></span></dt><dt><span class="sect2"><a href="sql-expressions.html#SYNTAX-EXPRESS-EVAL">4.2.14. Expression Evaluation Rules</a></span></dt></dl></div><a id="id-1.5.3.6.2" class="indexterm"></a><a id="id-1.5.3.6.3" class="indexterm"></a><a id="id-1.5.3.6.4" class="indexterm"></a><p>
3 Value expressions are used in a variety of contexts, such
4 as in the target list of the <code class="command">SELECT</code> command, as
5 new column values in <code class="command">INSERT</code> or
6 <code class="command">UPDATE</code>, or in search conditions in a number of
7 commands. The result of a value expression is sometimes called a
8 <em class="firstterm">scalar</em>, to distinguish it from the result of
9 a table expression (which is a table). Value expressions are
10 therefore also called <em class="firstterm">scalar expressions</em> (or
11 even simply <em class="firstterm">expressions</em>). The expression
12 syntax allows the calculation of values from primitive parts using
13 arithmetic, logical, set, and other operations.
15 A value expression is one of the following:
17 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
18 A constant or literal value
19 </p></li><li class="listitem"><p>
21 </p></li><li class="listitem"><p>
22 A positional parameter reference, in the body of a function definition
24 </p></li><li class="listitem"><p>
25 A subscripted expression
26 </p></li><li class="listitem"><p>
27 A field selection expression
28 </p></li><li class="listitem"><p>
29 An operator invocation
30 </p></li><li class="listitem"><p>
32 </p></li><li class="listitem"><p>
33 An aggregate expression
34 </p></li><li class="listitem"><p>
35 A window function call
36 </p></li><li class="listitem"><p>
38 </p></li><li class="listitem"><p>
39 A collation expression
40 </p></li><li class="listitem"><p>
42 </p></li><li class="listitem"><p>
44 </p></li><li class="listitem"><p>
46 </p></li><li class="listitem"><p>
47 Another value expression in parentheses (used to group
48 subexpressions and override
49 precedence<a id="id-1.5.3.6.6.1.15.1.1" class="indexterm"></a>)
50 </p></li></ul></div><p>
52 In addition to this list, there are a number of constructs that can
53 be classified as an expression but do not follow any general syntax
54 rules. These generally have the semantics of a function or
55 operator and are explained in the appropriate location in <a class="xref" href="functions.html" title="Chapter 9. Functions and Operators">Chapter 9</a>. An example is the <code class="literal">IS NULL</code>
58 We have already discussed constants in <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS" title="4.1.2. Constants">Section 4.1.2</a>. The following sections discuss
59 the remaining options.
60 </p><div class="sect2" id="SQL-EXPRESSIONS-COLUMN-REFS"><div class="titlepage"><div><div><h3 class="title">4.2.1. Column References <a href="#SQL-EXPRESSIONS-COLUMN-REFS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.9.2" class="indexterm"></a><p>
61 A column can be referenced in the form:
62 </p><pre class="synopsis">
63 <em class="replaceable"><code>correlation</code></em>.<em class="replaceable"><code>columnname</code></em>
66 <em class="replaceable"><code>correlation</code></em> is the name of a
67 table (possibly qualified with a schema name), or an alias for a table
68 defined by means of a <code class="literal">FROM</code> clause.
69 The correlation name and separating dot can be omitted if the column name
70 is unique across all the tables being used in the current query. (See also <a class="xref" href="queries.html" title="Chapter 7. Queries">Chapter 7</a>.)
71 </p></div><div class="sect2" id="SQL-EXPRESSIONS-PARAMETERS-POSITIONAL"><div class="titlepage"><div><div><h3 class="title">4.2.2. Positional Parameters <a href="#SQL-EXPRESSIONS-PARAMETERS-POSITIONAL" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.10.2" class="indexterm"></a><a id="id-1.5.3.6.10.3" class="indexterm"></a><p>
72 A positional parameter reference is used to indicate a value
73 that is supplied externally to an SQL statement. Parameters are
74 used in SQL function definitions and in prepared queries. Some
75 client libraries also support specifying data values separately
76 from the SQL command string, in which case parameters are used to
77 refer to the out-of-line data values.
78 The form of a parameter reference is:
79 </p><pre class="synopsis">
80 $<em class="replaceable"><code>number</code></em>
83 For example, consider the definition of a function,
84 <code class="function">dept</code>, as:
86 </p><pre class="programlisting">
87 CREATE FUNCTION dept(text) RETURNS dept
88 AS $$ SELECT * FROM dept WHERE name = $1 $$
92 Here the <code class="literal">$1</code> references the value of the first
93 function argument whenever the function is invoked.
94 </p></div><div class="sect2" id="SQL-EXPRESSIONS-SUBSCRIPTS"><div class="titlepage"><div><div><h3 class="title">4.2.3. Subscripts <a href="#SQL-EXPRESSIONS-SUBSCRIPTS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.11.2" class="indexterm"></a><p>
95 If an expression yields a value of an array type, then a specific
96 element of the array value can be extracted by writing
97 </p><pre class="synopsis">
98 <em class="replaceable"><code>expression</code></em>[<em class="replaceable"><code>subscript</code></em>]
100 or multiple adjacent elements (an <span class="quote">“<span class="quote">array slice</span>”</span>) can be extracted
102 </p><pre class="synopsis">
103 <em class="replaceable"><code>expression</code></em>[<em class="replaceable"><code>lower_subscript</code></em>:<em class="replaceable"><code>upper_subscript</code></em>]
105 (Here, the brackets <code class="literal">[ ]</code> are meant to appear literally.)
106 Each <em class="replaceable"><code>subscript</code></em> is itself an expression,
107 which will be rounded to the nearest integer value.
109 In general the array <em class="replaceable"><code>expression</code></em> must be
110 parenthesized, but the parentheses can be omitted when the expression
111 to be subscripted is just a column reference or positional parameter.
112 Also, multiple subscripts can be concatenated when the original array
116 </p><pre class="programlisting">
117 mytable.arraycolumn[4]
118 mytable.two_d_column[17][34]
120 (arrayfunction(a,b))[42]
123 The parentheses in the last example are required.
124 See <a class="xref" href="arrays.html" title="8.15. Arrays">Section 8.15</a> for more about arrays.
125 </p></div><div class="sect2" id="FIELD-SELECTION"><div class="titlepage"><div><div><h3 class="title">4.2.4. Field Selection <a href="#FIELD-SELECTION" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.12.2" class="indexterm"></a><p>
126 If an expression yields a value of a composite type (row type), then a
127 specific field of the row can be extracted by writing
128 </p><pre class="synopsis">
129 <em class="replaceable"><code>expression</code></em>.<em class="replaceable"><code>fieldname</code></em>
132 In general the row <em class="replaceable"><code>expression</code></em> must be
133 parenthesized, but the parentheses can be omitted when the expression
134 to be selected from is just a table reference or positional parameter.
137 </p><pre class="programlisting">
140 (rowfunction(a,b)).col3
143 (Thus, a qualified column reference is actually just a special case
144 of the field selection syntax.) An important special case is
145 extracting a field from a table column that is of a composite type:
147 </p><pre class="programlisting">
148 (compositecol).somefield
149 (mytable.compositecol).somefield
152 The parentheses are required here to show that
153 <code class="structfield">compositecol</code> is a column name not a table name,
154 or that <code class="structname">mytable</code> is a table name not a schema name
157 You can ask for all fields of a composite value by
158 writing <code class="literal">.*</code>:
159 </p><pre class="programlisting">
162 This notation behaves differently depending on context;
163 see <a class="xref" href="rowtypes.html#ROWTYPES-USAGE" title="8.16.5. Using Composite Types in Queries">Section 8.16.5</a> for details.
164 </p></div><div class="sect2" id="SQL-EXPRESSIONS-OPERATOR-CALLS"><div class="titlepage"><div><div><h3 class="title">4.2.5. Operator Invocations <a href="#SQL-EXPRESSIONS-OPERATOR-CALLS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.13.2" class="indexterm"></a><p>
165 There are two possible syntaxes for an operator invocation:
166 </p><table border="0" summary="Simple list" class="simplelist"><tr><td><em class="replaceable"><code>expression</code></em> <em class="replaceable"><code>operator</code></em> <em class="replaceable"><code>expression</code></em> (binary infix operator)</td></tr><tr><td><em class="replaceable"><code>operator</code></em> <em class="replaceable"><code>expression</code></em> (unary prefix operator)</td></tr></table><p>
167 where the <em class="replaceable"><code>operator</code></em> token follows the syntax
168 rules of <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS" title="4.1.3. Operators">Section 4.1.3</a>, or is one of the
169 key words <code class="token">AND</code>, <code class="token">OR</code>, and
170 <code class="token">NOT</code>, or is a qualified operator name in the form:
171 </p><pre class="synopsis">
172 <code class="literal">OPERATOR(</code><em class="replaceable"><code>schema</code></em><code class="literal">.</code><em class="replaceable"><code>operatorname</code></em><code class="literal">)</code>
174 Which particular operators exist and whether
175 they are unary or binary depends on what operators have been
176 defined by the system or the user. <a class="xref" href="functions.html" title="Chapter 9. Functions and Operators">Chapter 9</a>
177 describes the built-in operators.
178 </p></div><div class="sect2" id="SQL-EXPRESSIONS-FUNCTION-CALLS"><div class="titlepage"><div><div><h3 class="title">4.2.6. Function Calls <a href="#SQL-EXPRESSIONS-FUNCTION-CALLS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.14.2" class="indexterm"></a><p>
179 The syntax for a function call is the name of a function
180 (possibly qualified with a schema name), followed by its argument list
181 enclosed in parentheses:
183 </p><pre class="synopsis">
184 <em class="replaceable"><code>function_name</code></em> ([<span class="optional"><em class="replaceable"><code>expression</code></em> [<span class="optional">, <em class="replaceable"><code>expression</code></em> ... </span>]</span>] )
187 For example, the following computes the square root of 2:
188 </p><pre class="programlisting">
192 The list of built-in functions is in <a class="xref" href="functions.html" title="Chapter 9. Functions and Operators">Chapter 9</a>.
193 Other functions can be added by the user.
195 When issuing queries in a database where some users mistrust other users,
196 observe security precautions from <a class="xref" href="typeconv-func.html" title="10.3. Functions">Section 10.3</a> when
197 writing function calls.
199 The arguments can optionally have names attached.
200 See <a class="xref" href="sql-syntax-calling-funcs.html" title="4.3. Calling Functions">Section 4.3</a> for details.
201 </p><div class="note"><h3 class="title">Note</h3><p>
202 A function that takes a single argument of composite type can
203 optionally be called using field-selection syntax, and conversely
204 field selection can be written in functional style. That is, the
205 notations <code class="literal">col(table)</code> and <code class="literal">table.col</code> are
206 interchangeable. This behavior is not SQL-standard but is provided
207 in <span class="productname">PostgreSQL</span> because it allows use of functions to
208 emulate <span class="quote">“<span class="quote">computed fields</span>”</span>. For more information see
209 <a class="xref" href="rowtypes.html#ROWTYPES-USAGE" title="8.16.5. Using Composite Types in Queries">Section 8.16.5</a>.
210 </p></div></div><div class="sect2" id="SYNTAX-AGGREGATES"><div class="titlepage"><div><div><h3 class="title">4.2.7. Aggregate Expressions <a href="#SYNTAX-AGGREGATES" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.15.2" class="indexterm"></a><a id="id-1.5.3.6.15.3" class="indexterm"></a><a id="id-1.5.3.6.15.4" class="indexterm"></a><a id="id-1.5.3.6.15.5" class="indexterm"></a><p>
211 An <em class="firstterm">aggregate expression</em> represents the
212 application of an aggregate function across the rows selected by a
213 query. An aggregate function reduces multiple inputs to a single
214 output value, such as the sum or average of the inputs. The
215 syntax of an aggregate expression is one of the following:
217 </p><pre class="synopsis">
218 <em class="replaceable"><code>aggregate_name</code></em> (<em class="replaceable"><code>expression</code></em> [ , ... ] [ <em class="replaceable"><code>order_by_clause</code></em> ] ) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ]
219 <em class="replaceable"><code>aggregate_name</code></em> (ALL <em class="replaceable"><code>expression</code></em> [ , ... ] [ <em class="replaceable"><code>order_by_clause</code></em> ] ) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ]
220 <em class="replaceable"><code>aggregate_name</code></em> (DISTINCT <em class="replaceable"><code>expression</code></em> [ , ... ] [ <em class="replaceable"><code>order_by_clause</code></em> ] ) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ]
221 <em class="replaceable"><code>aggregate_name</code></em> ( * ) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ]
222 <em class="replaceable"><code>aggregate_name</code></em> ( [ <em class="replaceable"><code>expression</code></em> [ , ... ] ] ) WITHIN GROUP ( <em class="replaceable"><code>order_by_clause</code></em> ) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ]
225 where <em class="replaceable"><code>aggregate_name</code></em> is a previously
226 defined aggregate (possibly qualified with a schema name) and
227 <em class="replaceable"><code>expression</code></em> is
228 any value expression that does not itself contain an aggregate
229 expression or a window function call. The optional
230 <em class="replaceable"><code>order_by_clause</code></em> and
231 <em class="replaceable"><code>filter_clause</code></em> are described below.
233 The first form of aggregate expression invokes the aggregate
234 once for each input row.
235 The second form is the same as the first, since
236 <code class="literal">ALL</code> is the default.
237 The third form invokes the aggregate once for each distinct value
238 of the expression (or distinct set of values, for multiple expressions)
239 found in the input rows.
240 The fourth form invokes the aggregate once for each input row; since no
241 particular input value is specified, it is generally only useful
242 for the <code class="function">count(*)</code> aggregate function.
243 The last form is used with <em class="firstterm">ordered-set</em> aggregate
244 functions, which are described below.
246 Most aggregate functions ignore null inputs, so that rows in which
247 one or more of the expression(s) yield null are discarded. This
248 can be assumed to be true, unless otherwise specified, for all
251 For example, <code class="literal">count(*)</code> yields the total number
252 of input rows; <code class="literal">count(f1)</code> yields the number of
253 input rows in which <code class="literal">f1</code> is non-null, since
254 <code class="function">count</code> ignores nulls; and
255 <code class="literal">count(distinct f1)</code> yields the number of
256 distinct non-null values of <code class="literal">f1</code>.
258 Ordinarily, the input rows are fed to the aggregate function in an
259 unspecified order. In many cases this does not matter; for example,
260 <code class="function">min</code> produces the same result no matter what order it
261 receives the inputs in. However, some aggregate functions
262 (such as <code class="function">array_agg</code> and <code class="function">string_agg</code>) produce
263 results that depend on the ordering of the input rows. When using
264 such an aggregate, the optional <em class="replaceable"><code>order_by_clause</code></em> can be
265 used to specify the desired ordering. The <em class="replaceable"><code>order_by_clause</code></em>
266 has the same syntax as for a query-level <code class="literal">ORDER BY</code> clause, as
267 described in <a class="xref" href="queries-order.html" title="7.5. Sorting Rows (ORDER BY)">Section 7.5</a>, except that its expressions
268 are always just expressions and cannot be output-column names or numbers.
270 </p><pre class="programlisting">
271 WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
272 SELECT array_agg(v ORDER BY v DESC) FROM vals;
277 Since <code class="type">jsonb</code> only keeps the last matching key, ordering
278 of its keys can be significant:
279 </p><pre class="programlisting">
280 WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )
281 SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals;
283 ----------------------------
284 {"key0": "1", "key1": "3"}
287 When dealing with multiple-argument aggregate functions, note that the
288 <code class="literal">ORDER BY</code> clause goes after all the aggregate arguments.
289 For example, write this:
290 </p><pre class="programlisting">
291 SELECT string_agg(a, ',' ORDER BY a) FROM table;
294 </p><pre class="programlisting">
295 SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
297 The latter is syntactically valid, but it represents a call of a
298 single-argument aggregate function with two <code class="literal">ORDER BY</code> keys
299 (the second one being rather useless since it's a constant).
301 If <code class="literal">DISTINCT</code> is specified with an
302 <em class="replaceable"><code>order_by_clause</code></em>, <code class="literal">ORDER
303 BY</code> expressions can only reference columns in the
304 <code class="literal">DISTINCT</code> list. For example:
305 </p><pre class="programlisting">
306 WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
307 SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;
313 Placing <code class="literal">ORDER BY</code> within the aggregate's regular argument
314 list, as described so far, is used when ordering the input rows for
315 general-purpose and statistical aggregates, for which ordering is
317 subclass of aggregate functions called <em class="firstterm">ordered-set
318 aggregates</em> for which an <em class="replaceable"><code>order_by_clause</code></em>
319 is <span class="emphasis"><em>required</em></span>, usually because the aggregate's computation is
320 only sensible in terms of a specific ordering of its input rows.
321 Typical examples of ordered-set aggregates include rank and percentile
322 calculations. For an ordered-set aggregate,
323 the <em class="replaceable"><code>order_by_clause</code></em> is written
324 inside <code class="literal">WITHIN GROUP (...)</code>, as shown in the final syntax
325 alternative above. The expressions in
326 the <em class="replaceable"><code>order_by_clause</code></em> are evaluated once per
327 input row just like regular aggregate arguments, sorted as per
328 the <em class="replaceable"><code>order_by_clause</code></em>'s requirements, and fed
329 to the aggregate function as input arguments. (This is unlike the case
330 for a non-<code class="literal">WITHIN GROUP</code> <em class="replaceable"><code>order_by_clause</code></em>,
331 which is not treated as argument(s) to the aggregate function.) The
332 argument expressions preceding <code class="literal">WITHIN GROUP</code>, if any, are
333 called <em class="firstterm">direct arguments</em> to distinguish them from
334 the <em class="firstterm">aggregated arguments</em> listed in
335 the <em class="replaceable"><code>order_by_clause</code></em>. Unlike regular aggregate
336 arguments, direct arguments are evaluated only once per aggregate call,
337 not once per input row. This means that they can contain variables only
338 if those variables are grouped by <code class="literal">GROUP BY</code>; this restriction
339 is the same as if the direct arguments were not inside an aggregate
340 expression at all. Direct arguments are typically used for things like
341 percentile fractions, which only make sense as a single value per
342 aggregation calculation. The direct argument list can be empty; in this
343 case, write just <code class="literal">()</code> not <code class="literal">(*)</code>.
344 (<span class="productname">PostgreSQL</span> will actually accept either spelling, but
345 only the first way conforms to the SQL standard.)
347 <a id="id-1.5.3.6.15.14.1" class="indexterm"></a>
348 An example of an ordered-set aggregate call is:
350 </p><pre class="programlisting">
351 SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;
357 which obtains the 50th percentile, or median, value of
358 the <code class="structfield">income</code> column from table <code class="structname">households</code>.
359 Here, <code class="literal">0.5</code> is a direct argument; it would make no sense
360 for the percentile fraction to be a value varying across rows.
362 If <code class="literal">FILTER</code> is specified, then only the input
363 rows for which the <em class="replaceable"><code>filter_clause</code></em>
364 evaluates to true are fed to the aggregate function; other rows
365 are discarded. For example:
366 </p><pre class="programlisting">
368 count(*) AS unfiltered,
369 count(*) FILTER (WHERE i < 5) AS filtered
370 FROM generate_series(1,10) AS s(i);
371 unfiltered | filtered
372 ------------+----------
377 The predefined aggregate functions are described in <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a>. Other aggregate functions can be added
380 An aggregate expression can only appear in the result list or
381 <code class="literal">HAVING</code> clause of a <code class="command">SELECT</code> command.
382 It is forbidden in other clauses, such as <code class="literal">WHERE</code>,
383 because those clauses are logically evaluated before the results
384 of aggregates are formed.
386 When an aggregate expression appears in a subquery (see
387 <a class="xref" href="sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES" title="4.2.11. Scalar Subqueries">Section 4.2.11</a> and
388 <a class="xref" href="functions-subquery.html" title="9.24. Subquery Expressions">Section 9.24</a>), the aggregate is normally
389 evaluated over the rows of the subquery. But an exception occurs
390 if the aggregate's arguments (and <em class="replaceable"><code>filter_clause</code></em>
391 if any) contain only outer-level variables:
392 the aggregate then belongs to the nearest such outer level, and is
393 evaluated over the rows of that query. The aggregate expression
394 as a whole is then an outer reference for the subquery it appears in,
395 and acts as a constant over any one evaluation of that subquery.
396 The restriction about
397 appearing only in the result list or <code class="literal">HAVING</code> clause
398 applies with respect to the query level that the aggregate belongs to.
399 </p></div><div class="sect2" id="SYNTAX-WINDOW-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">4.2.8. Window Function Calls <a href="#SYNTAX-WINDOW-FUNCTIONS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.16.2" class="indexterm"></a><a id="id-1.5.3.6.16.3" class="indexterm"></a><p>
400 A <em class="firstterm">window function call</em> represents the application
401 of an aggregate-like function over some portion of the rows selected
402 by a query. Unlike non-window aggregate calls, this is not tied
403 to grouping of the selected rows into a single output row — each
404 row remains separate in the query output. However the window function
405 has access to all the rows that would be part of the current row's
406 group according to the grouping specification (<code class="literal">PARTITION BY</code>
407 list) of the window function call.
408 The syntax of a window function call is one of the following:
410 </p><pre class="synopsis">
411 <em class="replaceable"><code>function_name</code></em> ([<span class="optional"><em class="replaceable"><code>expression</code></em> [<span class="optional">, <em class="replaceable"><code>expression</code></em> ... </span>]</span>]) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ] OVER <em class="replaceable"><code>window_name</code></em>
412 <em class="replaceable"><code>function_name</code></em> ([<span class="optional"><em class="replaceable"><code>expression</code></em> [<span class="optional">, <em class="replaceable"><code>expression</code></em> ... </span>]</span>]) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ] OVER ( <em class="replaceable"><code>window_definition</code></em> )
413 <em class="replaceable"><code>function_name</code></em> ( * ) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ] OVER <em class="replaceable"><code>window_name</code></em>
414 <em class="replaceable"><code>function_name</code></em> ( * ) [ FILTER ( WHERE <em class="replaceable"><code>filter_clause</code></em> ) ] OVER ( <em class="replaceable"><code>window_definition</code></em> )
416 where <em class="replaceable"><code>window_definition</code></em>
418 </p><pre class="synopsis">
419 [ <em class="replaceable"><code>existing_window_name</code></em> ]
420 [ PARTITION BY <em class="replaceable"><code>expression</code></em> [, ...] ]
421 [ ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [ NULLS { FIRST | LAST } ] [, ...] ]
422 [ <em class="replaceable"><code>frame_clause</code></em> ]
424 The optional <em class="replaceable"><code>frame_clause</code></em>
426 </p><pre class="synopsis">
427 { RANGE | ROWS | GROUPS } <em class="replaceable"><code>frame_start</code></em> [ <em class="replaceable"><code>frame_exclusion</code></em> ]
428 { RANGE | ROWS | GROUPS } BETWEEN <em class="replaceable"><code>frame_start</code></em> AND <em class="replaceable"><code>frame_end</code></em> [ <em class="replaceable"><code>frame_exclusion</code></em> ]
430 where <em class="replaceable"><code>frame_start</code></em>
431 and <em class="replaceable"><code>frame_end</code></em> can be one of
432 </p><pre class="synopsis">
434 <em class="replaceable"><code>offset</code></em> PRECEDING
436 <em class="replaceable"><code>offset</code></em> FOLLOWING
439 and <em class="replaceable"><code>frame_exclusion</code></em> can be one of
440 </p><pre class="synopsis">
447 Here, <em class="replaceable"><code>expression</code></em> represents any value
448 expression that does not itself contain window function calls.
450 <em class="replaceable"><code>window_name</code></em> is a reference to a named window
451 specification defined in the query's <code class="literal">WINDOW</code> clause.
452 Alternatively, a full <em class="replaceable"><code>window_definition</code></em> can
453 be given within parentheses, using the same syntax as for defining a
454 named window in the <code class="literal">WINDOW</code> clause; see the
455 <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a> reference page for details. It's worth
456 pointing out that <code class="literal">OVER wname</code> is not exactly equivalent to
457 <code class="literal">OVER (wname ...)</code>; the latter implies copying and modifying the
458 window definition, and will be rejected if the referenced window
459 specification includes a frame clause.
461 The <code class="literal">PARTITION BY</code> clause groups the rows of the query into
462 <em class="firstterm">partitions</em>, which are processed separately by the window
463 function. <code class="literal">PARTITION BY</code> works similarly to a query-level
464 <code class="literal">GROUP BY</code> clause, except that its expressions are always just
465 expressions and cannot be output-column names or numbers.
466 Without <code class="literal">PARTITION BY</code>, all rows produced by the query are
467 treated as a single partition.
468 The <code class="literal">ORDER BY</code> clause determines the order in which the rows
469 of a partition are processed by the window function. It works similarly
470 to a query-level <code class="literal">ORDER BY</code> clause, but likewise cannot use
471 output-column names or numbers. Without <code class="literal">ORDER BY</code>, rows are
472 processed in an unspecified order.
474 The <em class="replaceable"><code>frame_clause</code></em> specifies
475 the set of rows constituting the <em class="firstterm">window frame</em>, which is a
476 subset of the current partition, for those window functions that act on
477 the frame instead of the whole partition. The set of rows in the frame
478 can vary depending on which row is the current row. The frame can be
479 specified in <code class="literal">RANGE</code>, <code class="literal">ROWS</code>
480 or <code class="literal">GROUPS</code> mode; in each case, it runs from
481 the <em class="replaceable"><code>frame_start</code></em> to
482 the <em class="replaceable"><code>frame_end</code></em>.
483 If <em class="replaceable"><code>frame_end</code></em> is omitted, the end defaults
484 to <code class="literal">CURRENT ROW</code>.
486 A <em class="replaceable"><code>frame_start</code></em> of <code class="literal">UNBOUNDED PRECEDING</code> means
487 that the frame starts with the first row of the partition, and similarly
488 a <em class="replaceable"><code>frame_end</code></em> of <code class="literal">UNBOUNDED FOLLOWING</code> means
489 that the frame ends with the last row of the partition.
491 In <code class="literal">RANGE</code> or <code class="literal">GROUPS</code> mode,
492 a <em class="replaceable"><code>frame_start</code></em> of
493 <code class="literal">CURRENT ROW</code> means the frame starts with the current
494 row's first <em class="firstterm">peer</em> row (a row that the
495 window's <code class="literal">ORDER BY</code> clause sorts as equivalent to the
496 current row), while a <em class="replaceable"><code>frame_end</code></em> of
497 <code class="literal">CURRENT ROW</code> means the frame ends with the current
499 In <code class="literal">ROWS</code> mode, <code class="literal">CURRENT ROW</code> simply
500 means the current row.
502 In the <em class="replaceable"><code>offset</code></em> <code class="literal">PRECEDING</code>
503 and <em class="replaceable"><code>offset</code></em> <code class="literal">FOLLOWING</code> frame
504 options, the <em class="replaceable"><code>offset</code></em> must be an expression not
505 containing any variables, aggregate functions, or window functions.
506 The meaning of the <em class="replaceable"><code>offset</code></em> depends on the
508 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
509 In <code class="literal">ROWS</code> mode,
510 the <em class="replaceable"><code>offset</code></em> must yield a non-null,
511 non-negative integer, and the option means that the frame starts or
512 ends the specified number of rows before or after the current row.
513 </p></li><li class="listitem"><p>
514 In <code class="literal">GROUPS</code> mode,
515 the <em class="replaceable"><code>offset</code></em> again must yield a non-null,
516 non-negative integer, and the option means that the frame starts or
517 ends the specified number of <em class="firstterm">peer groups</em>
518 before or after the current row's peer group, where a peer group is a
519 set of rows that are equivalent in the <code class="literal">ORDER BY</code>
520 ordering. (There must be an <code class="literal">ORDER BY</code> clause
521 in the window definition to use <code class="literal">GROUPS</code> mode.)
522 </p></li><li class="listitem"><p>
523 In <code class="literal">RANGE</code> mode, these options require that
524 the <code class="literal">ORDER BY</code> clause specify exactly one column.
525 The <em class="replaceable"><code>offset</code></em> specifies the maximum
526 difference between the value of that column in the current row and
527 its value in preceding or following rows of the frame. The data type
528 of the <em class="replaceable"><code>offset</code></em> expression varies depending
529 on the data type of the ordering column. For numeric ordering
530 columns it is typically of the same type as the ordering column,
531 but for datetime ordering columns it is an <code class="type">interval</code>.
532 For example, if the ordering column is of type <code class="type">date</code>
533 or <code class="type">timestamp</code>, one could write <code class="literal">RANGE BETWEEN
534 '1 day' PRECEDING AND '10 days' FOLLOWING</code>.
535 The <em class="replaceable"><code>offset</code></em> is still required to be
536 non-null and non-negative, though the meaning
537 of <span class="quote">“<span class="quote">non-negative</span>”</span> depends on its data type.
538 </p></li></ul></div><p>
539 In any case, the distance to the end of the frame is limited by the
540 distance to the end of the partition, so that for rows near the partition
541 ends the frame might contain fewer rows than elsewhere.
543 Notice that in both <code class="literal">ROWS</code> and <code class="literal">GROUPS</code>
544 mode, <code class="literal">0 PRECEDING</code> and <code class="literal">0 FOLLOWING</code>
545 are equivalent to <code class="literal">CURRENT ROW</code>. This normally holds
546 in <code class="literal">RANGE</code> mode as well, for an appropriate
547 data-type-specific meaning of <span class="quote">“<span class="quote">zero</span>”</span>.
549 The <em class="replaceable"><code>frame_exclusion</code></em> option allows rows around
550 the current row to be excluded from the frame, even if they would be
551 included according to the frame start and frame end options.
552 <code class="literal">EXCLUDE CURRENT ROW</code> excludes the current row from the
554 <code class="literal">EXCLUDE GROUP</code> excludes the current row and its
555 ordering peers from the frame.
556 <code class="literal">EXCLUDE TIES</code> excludes any peers of the current
557 row from the frame, but not the current row itself.
558 <code class="literal">EXCLUDE NO OTHERS</code> simply specifies explicitly the
559 default behavior of not excluding the current row or its peers.
561 The default framing option is <code class="literal">RANGE UNBOUNDED PRECEDING</code>,
562 which is the same as <code class="literal">RANGE BETWEEN UNBOUNDED PRECEDING AND
563 CURRENT ROW</code>. With <code class="literal">ORDER BY</code>, this sets the frame to be
564 all rows from the partition start up through the current row's last
565 <code class="literal">ORDER BY</code> peer. Without <code class="literal">ORDER BY</code>,
566 this means all rows of the partition are included in the window frame,
567 since all rows become peers of the current row.
569 Restrictions are that
570 <em class="replaceable"><code>frame_start</code></em> cannot be <code class="literal">UNBOUNDED FOLLOWING</code>,
571 <em class="replaceable"><code>frame_end</code></em> cannot be <code class="literal">UNBOUNDED PRECEDING</code>,
572 and the <em class="replaceable"><code>frame_end</code></em> choice cannot appear earlier in the
573 above list of <em class="replaceable"><code>frame_start</code></em>
574 and <em class="replaceable"><code>frame_end</code></em> options than
575 the <em class="replaceable"><code>frame_start</code></em> choice does — for example
576 <code class="literal">RANGE BETWEEN CURRENT ROW AND <em class="replaceable"><code>offset</code></em>
577 PRECEDING</code> is not allowed.
578 But, for example, <code class="literal">ROWS BETWEEN 7 PRECEDING AND 8
579 PRECEDING</code> is allowed, even though it would never select any
582 If <code class="literal">FILTER</code> is specified, then only the input
583 rows for which the <em class="replaceable"><code>filter_clause</code></em>
584 evaluates to true are fed to the window function; other rows
585 are discarded. Only window functions that are aggregates accept
586 a <code class="literal">FILTER</code> clause.
588 The built-in window functions are described in <a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.67. General-Purpose Window Functions">Table 9.67</a>. Other window functions can be added by
589 the user. Also, any built-in or user-defined general-purpose or
590 statistical aggregate can be used as a window function. (Ordered-set
591 and hypothetical-set aggregates cannot presently be used as window functions.)
593 The syntaxes using <code class="literal">*</code> are used for calling parameter-less
594 aggregate functions as window functions, for example
595 <code class="literal">count(*) OVER (PARTITION BY x ORDER BY y)</code>.
596 The asterisk (<code class="literal">*</code>) is customarily not used for
597 window-specific functions. Window-specific functions do not
598 allow <code class="literal">DISTINCT</code> or <code class="literal">ORDER BY</code> to be used within the
599 function argument list.
601 Window function calls are permitted only in the <code class="literal">SELECT</code>
602 list and the <code class="literal">ORDER BY</code> clause of the query.
604 More information about window functions can be found in
605 <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a>,
606 <a class="xref" href="functions-window.html" title="9.22. Window Functions">Section 9.22</a>, and
607 <a class="xref" href="queries-table-expressions.html#QUERIES-WINDOW" title="7.2.5. Window Function Processing">Section 7.2.5</a>.
608 </p></div><div class="sect2" id="SQL-SYNTAX-TYPE-CASTS"><div class="titlepage"><div><div><h3 class="title">4.2.9. Type Casts <a href="#SQL-SYNTAX-TYPE-CASTS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.17.2" class="indexterm"></a><a id="id-1.5.3.6.17.3" class="indexterm"></a><a id="id-1.5.3.6.17.4" class="indexterm"></a><p>
609 A type cast specifies a conversion from one data type to another.
610 <span class="productname">PostgreSQL</span> accepts two equivalent syntaxes
612 </p><pre class="synopsis">
613 CAST ( <em class="replaceable"><code>expression</code></em> AS <em class="replaceable"><code>type</code></em> )
614 <em class="replaceable"><code>expression</code></em>::<em class="replaceable"><code>type</code></em>
616 The <code class="literal">CAST</code> syntax conforms to SQL; the syntax with
617 <code class="literal">::</code> is historical <span class="productname">PostgreSQL</span>
620 When a cast is applied to a value expression of a known type, it
621 represents a run-time type conversion. The cast will succeed only
622 if a suitable type conversion operation has been defined. Notice that this
623 is subtly different from the use of casts with constants, as shown in
624 <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC" title="4.1.2.7. Constants of Other Types">Section 4.1.2.7</a>. A cast applied to an
625 unadorned string literal represents the initial assignment of a type
626 to a literal constant value, and so it will succeed for any type
627 (if the contents of the string literal are acceptable input syntax for the
630 An explicit type cast can usually be omitted if there is no ambiguity as
631 to the type that a value expression must produce (for example, when it is
632 assigned to a table column); the system will automatically apply a
633 type cast in such cases. However, automatic casting is only done for
634 casts that are marked <span class="quote">“<span class="quote">OK to apply implicitly</span>”</span>
635 in the system catalogs. Other casts must be invoked with
636 explicit casting syntax. This restriction is intended to prevent
637 surprising conversions from being applied silently.
639 It is also possible to specify a type cast using a function-like
641 </p><pre class="synopsis">
642 <em class="replaceable"><code>typename</code></em> ( <em class="replaceable"><code>expression</code></em> )
644 However, this only works for types whose names are also valid as
645 function names. For example, <code class="literal">double precision</code>
646 cannot be used this way, but the equivalent <code class="literal">float8</code>
647 can. Also, the names <code class="literal">interval</code>, <code class="literal">time</code>, and
648 <code class="literal">timestamp</code> can only be used in this fashion if they are
649 double-quoted, because of syntactic conflicts. Therefore, the use of
650 the function-like cast syntax leads to inconsistencies and should
652 </p><div class="note"><h3 class="title">Note</h3><p>
653 The function-like syntax is in fact just a function call. When
654 one of the two standard cast syntaxes is used to do a run-time
655 conversion, it will internally invoke a registered function to
656 perform the conversion. By convention, these conversion functions
657 have the same name as their output type, and thus the <span class="quote">“<span class="quote">function-like
658 syntax</span>”</span> is nothing more than a direct invocation of the underlying
659 conversion function. Obviously, this is not something that a portable
660 application should rely on. For further details see
661 <a class="xref" href="sql-createcast.html" title="CREATE CAST"><span class="refentrytitle">CREATE CAST</span></a>.
662 </p></div></div><div class="sect2" id="SQL-SYNTAX-COLLATE-EXPRS"><div class="titlepage"><div><div><h3 class="title">4.2.10. Collation Expressions <a href="#SQL-SYNTAX-COLLATE-EXPRS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.18.2" class="indexterm"></a><p>
663 The <code class="literal">COLLATE</code> clause overrides the collation of
664 an expression. It is appended to the expression it applies to:
665 </p><pre class="synopsis">
666 <em class="replaceable"><code>expr</code></em> COLLATE <em class="replaceable"><code>collation</code></em>
668 where <em class="replaceable"><code>collation</code></em> is a possibly
669 schema-qualified identifier. The <code class="literal">COLLATE</code>
670 clause binds tighter than operators; parentheses can be used when
673 If no collation is explicitly specified, the database system
674 either derives a collation from the columns involved in the
675 expression, or it defaults to the default collation of the
676 database if no column is involved in the expression.
678 The two common uses of the <code class="literal">COLLATE</code> clause are
679 overriding the sort order in an <code class="literal">ORDER BY</code> clause, for
681 </p><pre class="programlisting">
682 SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";
684 and overriding the collation of a function or operator call that
685 has locale-sensitive results, for example:
686 </p><pre class="programlisting">
687 SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";
689 Note that in the latter case the <code class="literal">COLLATE</code> clause is
690 attached to an input argument of the operator we wish to affect.
691 It doesn't matter which argument of the operator or function call the
692 <code class="literal">COLLATE</code> clause is attached to, because the collation that is
693 applied by the operator or function is derived by considering all
694 arguments, and an explicit <code class="literal">COLLATE</code> clause will override the
695 collations of all other arguments. (Attaching non-matching
696 <code class="literal">COLLATE</code> clauses to more than one argument, however, is an
697 error. For more details see <a class="xref" href="collation.html" title="23.2. Collation Support">Section 23.2</a>.)
698 Thus, this gives the same result as the previous example:
699 </p><pre class="programlisting">
700 SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';
702 But this is an error:
703 </p><pre class="programlisting">
704 SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";
706 because it attempts to apply a collation to the result of the
707 <code class="literal">></code> operator, which is of the non-collatable data type
708 <code class="type">boolean</code>.
709 </p></div><div class="sect2" id="SQL-SYNTAX-SCALAR-SUBQUERIES"><div class="titlepage"><div><div><h3 class="title">4.2.11. Scalar Subqueries <a href="#SQL-SYNTAX-SCALAR-SUBQUERIES" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.19.2" class="indexterm"></a><p>
710 A scalar subquery is an ordinary
711 <code class="command">SELECT</code> query in parentheses that returns exactly one
712 row with one column. (See <a class="xref" href="queries.html" title="Chapter 7. Queries">Chapter 7</a> for information about writing queries.)
713 The <code class="command">SELECT</code> query is executed
714 and the single returned value is used in the surrounding value expression.
715 It is an error to use a query that
716 returns more than one row or more than one column as a scalar subquery.
717 (But if, during a particular execution, the subquery returns no rows,
718 there is no error; the scalar result is taken to be null.)
719 The subquery can refer to variables from the surrounding query,
720 which will act as constants during any one evaluation of the subquery.
721 See also <a class="xref" href="functions-subquery.html" title="9.24. Subquery Expressions">Section 9.24</a> for other expressions involving subqueries.
723 For example, the following finds the largest city population in each
725 </p><pre class="programlisting">
726 SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
729 </p></div><div class="sect2" id="SQL-SYNTAX-ARRAY-CONSTRUCTORS"><div class="titlepage"><div><div><h3 class="title">4.2.12. Array Constructors <a href="#SQL-SYNTAX-ARRAY-CONSTRUCTORS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.20.2" class="indexterm"></a><a id="id-1.5.3.6.20.3" class="indexterm"></a><p>
730 An array constructor is an expression that builds an
731 array value using values for its member elements. A simple array
733 consists of the key word <code class="literal">ARRAY</code>, a left square bracket
734 <code class="literal">[</code>, a list of expressions (separated by commas) for the
735 array element values, and finally a right square bracket <code class="literal">]</code>.
737 </p><pre class="programlisting">
738 SELECT ARRAY[1,2,3+4];
745 the array element type is the common type of the member expressions,
746 determined using the same rules as for <code class="literal">UNION</code> or
747 <code class="literal">CASE</code> constructs (see <a class="xref" href="typeconv-union-case.html" title="10.5. UNION, CASE, and Related Constructs">Section 10.5</a>).
748 You can override this by explicitly casting the array constructor to the
749 desired type, for example:
750 </p><pre class="programlisting">
751 SELECT ARRAY[1,2,22.7]::integer[];
757 This has the same effect as casting each expression to the array
758 element type individually.
759 For more on casting, see <a class="xref" href="sql-expressions.html#SQL-SYNTAX-TYPE-CASTS" title="4.2.9. Type Casts">Section 4.2.9</a>.
761 Multidimensional array values can be built by nesting array
763 In the inner constructors, the key word <code class="literal">ARRAY</code> can
764 be omitted. For example, these produce the same result:
766 </p><pre class="programlisting">
767 SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
773 SELECT ARRAY[[1,2],[3,4]];
780 Since multidimensional arrays must be rectangular, inner constructors
781 at the same level must produce sub-arrays of identical dimensions.
782 Any cast applied to the outer <code class="literal">ARRAY</code> constructor propagates
783 automatically to all the inner constructors.
785 Multidimensional array constructor elements can be anything yielding
786 an array of the proper kind, not only a sub-<code class="literal">ARRAY</code> construct.
788 </p><pre class="programlisting">
789 CREATE TABLE arr(f1 int[], f2 int[]);
791 INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);
793 SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;
795 ------------------------------------------------
796 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
800 You can construct an empty array, but since it's impossible to have an
801 array with no type, you must explicitly cast your empty array to the
802 desired type. For example:
803 </p><pre class="programlisting">
804 SELECT ARRAY[]::integer[];
811 It is also possible to construct an array from the results of a
812 subquery. In this form, the array constructor is written with the
813 key word <code class="literal">ARRAY</code> followed by a parenthesized (not
814 bracketed) subquery. For example:
815 </p><pre class="programlisting">
816 SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
818 ------------------------------------------------------------------
819 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412}
822 SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));
824 ----------------------------------
825 {{1,2},{2,4},{3,6},{4,8},{5,10}}
828 The subquery must return a single column.
829 If the subquery's output column is of a non-array type, the resulting
830 one-dimensional array will have an element for each row in the
831 subquery result, with an element type matching that of the
832 subquery's output column.
833 If the subquery's output column is of an array type, the result will be
834 an array of the same type but one higher dimension; in this case all
835 the subquery rows must yield arrays of identical dimensionality, else
836 the result would not be rectangular.
838 The subscripts of an array value built with <code class="literal">ARRAY</code>
839 always begin with one. For more information about arrays, see
840 <a class="xref" href="arrays.html" title="8.15. Arrays">Section 8.15</a>.
841 </p></div><div class="sect2" id="SQL-SYNTAX-ROW-CONSTRUCTORS"><div class="titlepage"><div><div><h3 class="title">4.2.13. Row Constructors <a href="#SQL-SYNTAX-ROW-CONSTRUCTORS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.21.2" class="indexterm"></a><a id="id-1.5.3.6.21.3" class="indexterm"></a><a id="id-1.5.3.6.21.4" class="indexterm"></a><p>
842 A row constructor is an expression that builds a row value (also
843 called a composite value) using values
844 for its member fields. A row constructor consists of the key word
845 <code class="literal">ROW</code>, a left parenthesis, zero or more
846 expressions (separated by commas) for the row field values, and finally
847 a right parenthesis. For example:
848 </p><pre class="programlisting">
849 SELECT ROW(1,2.5,'this is a test');
851 The key word <code class="literal">ROW</code> is optional when there is more than one
852 expression in the list.
854 A row constructor can include the syntax
855 <em class="replaceable"><code>rowvalue</code></em><code class="literal">.*</code>,
856 which will be expanded to a list of the elements of the row value,
857 just as occurs when the <code class="literal">.*</code> syntax is used at the top level
858 of a <code class="command">SELECT</code> list (see <a class="xref" href="rowtypes.html#ROWTYPES-USAGE" title="8.16.5. Using Composite Types in Queries">Section 8.16.5</a>).
859 For example, if table <code class="literal">t</code> has
860 columns <code class="literal">f1</code> and <code class="literal">f2</code>, these are the same:
861 </p><pre class="programlisting">
862 SELECT ROW(t.*, 42) FROM t;
863 SELECT ROW(t.f1, t.f2, 42) FROM t;
865 </p><div class="note"><h3 class="title">Note</h3><p>
866 Before <span class="productname">PostgreSQL</span> 8.2, the
867 <code class="literal">.*</code> syntax was not expanded in row constructors, so
868 that writing <code class="literal">ROW(t.*, 42)</code> created a two-field row whose first
869 field was another row value. The new behavior is usually more useful.
870 If you need the old behavior of nested row values, write the inner
871 row value without <code class="literal">.*</code>, for instance
872 <code class="literal">ROW(t, 42)</code>.
874 By default, the value created by a <code class="literal">ROW</code> expression is of
875 an anonymous record type. If necessary, it can be cast to a named
876 composite type — either the row type of a table, or a composite type
877 created with <code class="command">CREATE TYPE AS</code>. An explicit cast might be needed
878 to avoid ambiguity. For example:
879 </p><pre class="programlisting">
880 CREATE TABLE mytable(f1 int, f2 float, f3 text);
882 CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;
884 -- No cast needed since only one getf1() exists
885 SELECT getf1(ROW(1,2.5,'this is a test'));
891 CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
893 CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;
895 -- Now we need a cast to indicate which function to call:
896 SELECT getf1(ROW(1,2.5,'this is a test'));
897 ERROR: function getf1(record) is not unique
899 SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
905 SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
912 Row constructors can be used to build composite values to be stored
913 in a composite-type table column, or to be passed to a function that
914 accepts a composite parameter. Also, it is possible to test rows
915 using the standard comparison operators as described in <a class="xref" href="functions-comparison.html" title="9.2. Comparison Functions and Operators">Section 9.2</a>, to compare one row against another
916 as described in <a class="xref" href="functions-comparisons.html" title="9.25. Row and Array Comparisons">Section 9.25</a>, and to
917 use them in connection with subqueries, as discussed in <a class="xref" href="functions-subquery.html" title="9.24. Subquery Expressions">Section 9.24</a>.
918 </p></div><div class="sect2" id="SYNTAX-EXPRESS-EVAL"><div class="titlepage"><div><div><h3 class="title">4.2.14. Expression Evaluation Rules <a href="#SYNTAX-EXPRESS-EVAL" class="id_link">#</a></h3></div></div></div><a id="id-1.5.3.6.22.2" class="indexterm"></a><p>
919 The order of evaluation of subexpressions is not defined. In
920 particular, the inputs of an operator or function are not necessarily
921 evaluated left-to-right or in any other fixed order.
923 Furthermore, if the result of an expression can be determined by
924 evaluating only some parts of it, then other subexpressions
925 might not be evaluated at all. For instance, if one wrote:
926 </p><pre class="programlisting">
927 SELECT true OR somefunc();
929 then <code class="literal">somefunc()</code> would (probably) not be called
930 at all. The same would be the case if one wrote:
931 </p><pre class="programlisting">
932 SELECT somefunc() OR true;
934 Note that this is not the same as the left-to-right
935 <span class="quote">“<span class="quote">short-circuiting</span>”</span> of Boolean operators that is found
936 in some programming languages.
938 As a consequence, it is unwise to use functions with side effects
939 as part of complex expressions. It is particularly dangerous to
940 rely on side effects or evaluation order in <code class="literal">WHERE</code> and <code class="literal">HAVING</code> clauses,
941 since those clauses are extensively reprocessed as part of
942 developing an execution plan. Boolean
943 expressions (<code class="literal">AND</code>/<code class="literal">OR</code>/<code class="literal">NOT</code> combinations) in those clauses can be reorganized
944 in any manner allowed by the laws of Boolean algebra.
946 When it is essential to force evaluation order, a <code class="literal">CASE</code>
947 construct (see <a class="xref" href="functions-conditional.html" title="9.18. Conditional Expressions">Section 9.18</a>) can be
948 used. For example, this is an untrustworthy way of trying to
949 avoid division by zero in a <code class="literal">WHERE</code> clause:
950 </p><pre class="programlisting">
951 SELECT ... WHERE x > 0 AND y/x > 1.5;
954 </p><pre class="programlisting">
955 SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
957 A <code class="literal">CASE</code> construct used in this fashion will defeat optimization
958 attempts, so it should only be done when necessary. (In this particular
959 example, it would be better to sidestep the problem by writing
960 <code class="literal">y > 1.5*x</code> instead.)
962 <code class="literal">CASE</code> is not a cure-all for such issues, however.
963 One limitation of the technique illustrated above is that it does not
964 prevent early evaluation of constant subexpressions.
965 As described in <a class="xref" href="xfunc-volatility.html" title="36.7. Function Volatility Categories">Section 36.7</a>, functions and
966 operators marked <code class="literal">IMMUTABLE</code> can be evaluated when
967 the query is planned rather than when it is executed. Thus for example
968 </p><pre class="programlisting">
969 SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;
971 is likely to result in a division-by-zero failure due to the planner
972 trying to simplify the constant subexpression,
973 even if every row in the table has <code class="literal">x > 0</code> so that the
974 <code class="literal">ELSE</code> arm would never be entered at run time.
976 While that particular example might seem silly, related cases that don't
977 obviously involve constants can occur in queries executed within
978 functions, since the values of function arguments and local variables
979 can be inserted into queries as constants for planning purposes.
980 Within <span class="application">PL/pgSQL</span> functions, for example, using an
981 <code class="literal">IF</code>-<code class="literal">THEN</code>-<code class="literal">ELSE</code> statement to protect
982 a risky computation is much safer than just nesting it in a
983 <code class="literal">CASE</code> expression.
985 Another limitation of the same kind is that a <code class="literal">CASE</code> cannot
986 prevent evaluation of an aggregate expression contained within it,
987 because aggregate expressions are computed before other
988 expressions in a <code class="literal">SELECT</code> list or <code class="literal">HAVING</code> clause
989 are considered. For example, the following query can cause a
990 division-by-zero error despite seemingly having protected against it:
991 </p><pre class="programlisting">
992 SELECT CASE WHEN min(employees) > 0
993 THEN avg(expenses / employees)
997 The <code class="function">min()</code> and <code class="function">avg()</code> aggregates are computed
998 concurrently over all the input rows, so if any row
999 has <code class="structfield">employees</code> equal to zero, the division-by-zero error
1000 will occur before there is any opportunity to test the result of
1001 <code class="function">min()</code>. Instead, use a <code class="literal">WHERE</code>
1002 or <code class="literal">FILTER</code> clause to prevent problematic input rows from
1003 reaching an aggregate function in the first place.
1004 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-syntax-lexical.html" title="4.1. Lexical Structure">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-syntax.html" title="Chapter 4. SQL Syntax">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-syntax-calling-funcs.html" title="4.3. Calling Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">4.1. Lexical Structure </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 4.3. Calling Functions</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob