codekingpro/portable-devtools
115k
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 16.3 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, such4 as in the target list of the <code class="command">SELECT</code> command, as5 new column values in <code class="command">INSERT</code> or6 <code class="command">UPDATE</code>, or in search conditions in a number of7 commands. The result of a value expression is sometimes called a8 <em class="firstterm">scalar</em>, to distinguish it from the result of9 a table expression (which is a table). Value expressions are10 therefore also called <em class="firstterm">scalar expressions</em> (or11 even simply <em class="firstterm">expressions</em>). The expression12 syntax allows the calculation of values from primitive parts using13 arithmetic, logical, set, and other operations.14 </p><p>15 A value expression is one of the following:16 17 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>18 A constant or literal value19 </p></li><li class="listitem"><p>20 A column reference21 </p></li><li class="listitem"><p>22 A positional parameter reference, in the body of a function definition23 or prepared statement24 </p></li><li class="listitem"><p>25 A subscripted expression26 </p></li><li class="listitem"><p>27 A field selection expression28 </p></li><li class="listitem"><p>29 An operator invocation30 </p></li><li class="listitem"><p>31 A function call32 </p></li><li class="listitem"><p>33 An aggregate expression34 </p></li><li class="listitem"><p>35 A window function call36 </p></li><li class="listitem"><p>37 A type cast38 </p></li><li class="listitem"><p>39 A collation expression40 </p></li><li class="listitem"><p>41 A scalar subquery42 </p></li><li class="listitem"><p>43 An array constructor44 </p></li><li class="listitem"><p>45 A row constructor46 </p></li><li class="listitem"><p>47 Another value expression in parentheses (used to group48 subexpressions and override49 precedence<a id="id-1.5.3.6.6.1.15.1.1" class="indexterm"></a>)50 </p></li></ul></div><p>51 </p><p>52 In addition to this list, there are a number of constructs that can53 be classified as an expression but do not follow any general syntax54 rules. These generally have the semantics of a function or55 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>56 clause.57 </p><p>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 discuss59 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>64</pre><p>65 </p><p>66 <em class="replaceable"><code>correlation</code></em> is the name of a67 table (possibly qualified with a schema name), or an alias for a table68 defined by means of a <code class="literal">FROM</code> clause.69 The correlation name and separating dot can be omitted if the column name70 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 value73 that is supplied externally to an SQL statement. Parameters are74 used in SQL function definitions and in prepared queries. Some75 client libraries also support specifying data values separately76 from the SQL command string, in which case parameters are used to77 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>81</pre><p>82 </p><p>83 For example, consider the definition of a function,84 <code class="function">dept</code>, as:85 86</p><pre class="programlisting">87CREATE FUNCTION dept(text) RETURNS dept88 AS $$ SELECT * FROM dept WHERE name = $1 $$89 LANGUAGE SQL;90</pre><p>91 92 Here the <code class="literal">$1</code> references the value of the first93 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 specific96 element of the array value can be extracted by writing97</p><pre class="synopsis">98<em class="replaceable"><code>expression</code></em>[<em class="replaceable"><code>subscript</code></em>]99</pre><p>100 or multiple adjacent elements (an <span class="quote">“<span class="quote">array slice</span>”</span>) can be extracted101 by writing102</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>]104</pre><p>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.108 </p><p>109 In general the array <em class="replaceable"><code>expression</code></em> must be110 parenthesized, but the parentheses can be omitted when the expression111 to be subscripted is just a column reference or positional parameter.112 Also, multiple subscripts can be concatenated when the original array113 is multidimensional.114 For example:115 116</p><pre class="programlisting">117mytable.arraycolumn[4]118mytable.two_d_column[17][34]119$1[10:42]120(arrayfunction(a,b))[42]121</pre><p>122 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 a127 specific field of the row can be extracted by writing128</p><pre class="synopsis">129<em class="replaceable"><code>expression</code></em>.<em class="replaceable"><code>fieldname</code></em>130</pre><p>131 </p><p>132 In general the row <em class="replaceable"><code>expression</code></em> must be133 parenthesized, but the parentheses can be omitted when the expression134 to be selected from is just a table reference or positional parameter.135 For example:136 137</p><pre class="programlisting">138mytable.mycolumn139$1.somecolumn140(rowfunction(a,b)).col3141</pre><p>142 143 (Thus, a qualified column reference is actually just a special case144 of the field selection syntax.) An important special case is145 extracting a field from a table column that is of a composite type:146 147</p><pre class="programlisting">148(compositecol).somefield149(mytable.compositecol).somefield150</pre><p>151 152 The parentheses are required here to show that153 <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 name155 in the second case.156 </p><p>157 You can ask for all fields of a composite value by158 writing <code class="literal">.*</code>:159</p><pre class="programlisting">160(compositecol).*161</pre><p>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 syntax168 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 the169 key words <code class="token">AND</code>, <code class="token">OR</code>, and170 <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>173</pre><p>174 Which particular operators exist and whether175 they are unary or binary depends on what operators have been176 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 function180 (possibly qualified with a schema name), followed by its argument list181 enclosed in parentheses:182 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>] )185</pre><p>186 </p><p>187 For example, the following computes the square root of 2:188</p><pre class="programlisting">189sqrt(2)190</pre><p>191 </p><p>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.194 </p><p>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> when197 writing function calls.198 </p><p>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 can203 optionally be called using field-selection syntax, and conversely204 field selection can be written in functional style. That is, the205 notations <code class="literal">col(table)</code> and <code class="literal">table.col</code> are206 interchangeable. This behavior is not SQL-standard but is provided207 in <span class="productname">PostgreSQL</span> because it allows use of functions to208 emulate <span class="quote">“<span class="quote">computed fields</span>”</span>. For more information see209 <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 the212 application of an aggregate function across the rows selected by a213 query. An aggregate function reduces multiple inputs to a single214 output value, such as the sum or average of the inputs. The215 syntax of an aggregate expression is one of the following:216 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> ) ]223</pre><p>224 225 where <em class="replaceable"><code>aggregate_name</code></em> is a previously226 defined aggregate (possibly qualified with a schema name) and227 <em class="replaceable"><code>expression</code></em> is228 any value expression that does not itself contain an aggregate229 expression or a window function call. The optional230 <em class="replaceable"><code>order_by_clause</code></em> and231 <em class="replaceable"><code>filter_clause</code></em> are described below.232 </p><p>233 The first form of aggregate expression invokes the aggregate234 once for each input row.235 The second form is the same as the first, since236 <code class="literal">ALL</code> is the default.237 The third form invokes the aggregate once for each distinct value238 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 no241 particular input value is specified, it is generally only useful242 for the <code class="function">count(*)</code> aggregate function.243 The last form is used with <em class="firstterm">ordered-set</em> aggregate244 functions, which are described below.245 </p><p>246 Most aggregate functions ignore null inputs, so that rows in which247 one or more of the expression(s) yield null are discarded. This248 can be assumed to be true, unless otherwise specified, for all249 built-in aggregates.250 </p><p>251 For example, <code class="literal">count(*)</code> yields the total number252 of input rows; <code class="literal">count(f1)</code> yields the number of253 input rows in which <code class="literal">f1</code> is non-null, since254 <code class="function">count</code> ignores nulls; and255 <code class="literal">count(distinct f1)</code> yields the number of256 distinct non-null values of <code class="literal">f1</code>.257 </p><p>258 Ordinarily, the input rows are fed to the aggregate function in an259 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 it261 receives the inputs in. However, some aggregate functions262 (such as <code class="function">array_agg</code> and <code class="function">string_agg</code>) produce263 results that depend on the ordering of the input rows. When using264 such an aggregate, the optional <em class="replaceable"><code>order_by_clause</code></em> can be265 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, as267 described in <a class="xref" href="queries-order.html" title="7.5. Sorting Rows (ORDER BY)">Section 7.5</a>, except that its expressions268 are always just expressions and cannot be output-column names or numbers.269 For example:270</p><pre class="programlisting">271SELECT array_agg(a ORDER BY b DESC) FROM table;272</pre><p>273 </p><p>274 When dealing with multiple-argument aggregate functions, note that the275 <code class="literal">ORDER BY</code> clause goes after all the aggregate arguments.276 For example, write this:277</p><pre class="programlisting">278SELECT string_agg(a, ',' ORDER BY a) FROM table;279</pre><p>280 not this:281</p><pre class="programlisting">282SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect283</pre><p>284 The latter is syntactically valid, but it represents a call of a285 single-argument aggregate function with two <code class="literal">ORDER BY</code> keys286 (the second one being rather useless since it's a constant).287 </p><p>288 If <code class="literal">DISTINCT</code> is specified in addition to an289 <em class="replaceable"><code>order_by_clause</code></em>, then all the <code class="literal">ORDER BY</code>290 expressions must match regular arguments of the aggregate; that is,291 you cannot sort on an expression that is not included in the292 <code class="literal">DISTINCT</code> list.293 </p><div class="note"><h3 class="title">Note</h3><p>294 The ability to specify both <code class="literal">DISTINCT</code> and <code class="literal">ORDER BY</code>295 in an aggregate function is a <span class="productname">PostgreSQL</span> extension.296 </p></div><p>297 Placing <code class="literal">ORDER BY</code> within the aggregate's regular argument298 list, as described so far, is used when ordering the input rows for299 general-purpose and statistical aggregates, for which ordering is300 optional. There is a301 subclass of aggregate functions called <em class="firstterm">ordered-set302 aggregates</em> for which an <em class="replaceable"><code>order_by_clause</code></em>303 is <span class="emphasis"><em>required</em></span>, usually because the aggregate's computation is304 only sensible in terms of a specific ordering of its input rows.305 Typical examples of ordered-set aggregates include rank and percentile306 calculations. For an ordered-set aggregate,307 the <em class="replaceable"><code>order_by_clause</code></em> is written308 inside <code class="literal">WITHIN GROUP (...)</code>, as shown in the final syntax309 alternative above. The expressions in310 the <em class="replaceable"><code>order_by_clause</code></em> are evaluated once per311 input row just like regular aggregate arguments, sorted as per312 the <em class="replaceable"><code>order_by_clause</code></em>'s requirements, and fed313 to the aggregate function as input arguments. (This is unlike the case314 for a non-<code class="literal">WITHIN GROUP</code> <em class="replaceable"><code>order_by_clause</code></em>,315 which is not treated as argument(s) to the aggregate function.) The316 argument expressions preceding <code class="literal">WITHIN GROUP</code>, if any, are317 called <em class="firstterm">direct arguments</em> to distinguish them from318 the <em class="firstterm">aggregated arguments</em> listed in319 the <em class="replaceable"><code>order_by_clause</code></em>. Unlike regular aggregate320 arguments, direct arguments are evaluated only once per aggregate call,321 not once per input row. This means that they can contain variables only322 if those variables are grouped by <code class="literal">GROUP BY</code>; this restriction323 is the same as if the direct arguments were not inside an aggregate324 expression at all. Direct arguments are typically used for things like325 percentile fractions, which only make sense as a single value per326 aggregation calculation. The direct argument list can be empty; in this327 case, write just <code class="literal">()</code> not <code class="literal">(*)</code>.328 (<span class="productname">PostgreSQL</span> will actually accept either spelling, but329 only the first way conforms to the SQL standard.)330 </p><p>331 <a id="id-1.5.3.6.15.15.1" class="indexterm"></a>332 An example of an ordered-set aggregate call is:333 334</p><pre class="programlisting">335SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;336 percentile_cont337-----------------338 50489339</pre><p>340 341 which obtains the 50th percentile, or median, value of342 the <code class="structfield">income</code> column from table <code class="structname">households</code>.343 Here, <code class="literal">0.5</code> is a direct argument; it would make no sense344 for the percentile fraction to be a value varying across rows.345 </p><p>346 If <code class="literal">FILTER</code> is specified, then only the input347 rows for which the <em class="replaceable"><code>filter_clause</code></em>348 evaluates to true are fed to the aggregate function; other rows349 are discarded. For example:350</p><pre class="programlisting">351SELECT352 count(*) AS unfiltered,353 count(*) FILTER (WHERE i < 5) AS filtered354FROM generate_series(1,10) AS s(i);355 unfiltered | filtered356------------+----------357 10 | 4358(1 row)359</pre><p>360 </p><p>361 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 added362 by the user.363 </p><p>364 An aggregate expression can only appear in the result list or365 <code class="literal">HAVING</code> clause of a <code class="command">SELECT</code> command.366 It is forbidden in other clauses, such as <code class="literal">WHERE</code>,367 because those clauses are logically evaluated before the results368 of aggregates are formed.369 </p><p>370 When an aggregate expression appears in a subquery (see371 <a class="xref" href="sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES" title="4.2.11. Scalar Subqueries">Section 4.2.11</a> and372 <a class="xref" href="functions-subquery.html" title="9.23. Subquery Expressions">Section 9.23</a>), the aggregate is normally373 evaluated over the rows of the subquery. But an exception occurs374 if the aggregate's arguments (and <em class="replaceable"><code>filter_clause</code></em>375 if any) contain only outer-level variables:376 the aggregate then belongs to the nearest such outer level, and is377 evaluated over the rows of that query. The aggregate expression378 as a whole is then an outer reference for the subquery it appears in,379 and acts as a constant over any one evaluation of that subquery.380 The restriction about381 appearing only in the result list or <code class="literal">HAVING</code> clause382 applies with respect to the query level that the aggregate belongs to.383 </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>384 A <em class="firstterm">window function call</em> represents the application385 of an aggregate-like function over some portion of the rows selected386 by a query. Unlike non-window aggregate calls, this is not tied387 to grouping of the selected rows into a single output row — each388 row remains separate in the query output. However the window function389 has access to all the rows that would be part of the current row's390 group according to the grouping specification (<code class="literal">PARTITION BY</code>391 list) of the window function call.392 The syntax of a window function call is one of the following:393 394</p><pre class="synopsis">395<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>396<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> )397<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>398<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> )399</pre><p>400 where <em class="replaceable"><code>window_definition</code></em>401 has the syntax402</p><pre class="synopsis">403[ <em class="replaceable"><code>existing_window_name</code></em> ]404[ PARTITION BY <em class="replaceable"><code>expression</code></em> [, ...] ]405[ ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [ NULLS { FIRST | LAST } ] [, ...] ]406[ <em class="replaceable"><code>frame_clause</code></em> ]407</pre><p>408 The optional <em class="replaceable"><code>frame_clause</code></em>409 can be one of410</p><pre class="synopsis">411{ RANGE | ROWS | GROUPS } <em class="replaceable"><code>frame_start</code></em> [ <em class="replaceable"><code>frame_exclusion</code></em> ]412{ 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> ]413</pre><p>414 where <em class="replaceable"><code>frame_start</code></em>415 and <em class="replaceable"><code>frame_end</code></em> can be one of416</p><pre class="synopsis">417UNBOUNDED PRECEDING418<em class="replaceable"><code>offset</code></em> PRECEDING419CURRENT ROW420<em class="replaceable"><code>offset</code></em> FOLLOWING421UNBOUNDED FOLLOWING422</pre><p>423 and <em class="replaceable"><code>frame_exclusion</code></em> can be one of424</p><pre class="synopsis">425EXCLUDE CURRENT ROW426EXCLUDE GROUP427EXCLUDE TIES428EXCLUDE NO OTHERS429</pre><p>430 </p><p>431 Here, <em class="replaceable"><code>expression</code></em> represents any value432 expression that does not itself contain window function calls.433 </p><p>434 <em class="replaceable"><code>window_name</code></em> is a reference to a named window435 specification defined in the query's <code class="literal">WINDOW</code> clause.436 Alternatively, a full <em class="replaceable"><code>window_definition</code></em> can437 be given within parentheses, using the same syntax as for defining a438 named window in the <code class="literal">WINDOW</code> clause; see the439 <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a> reference page for details. It's worth440 pointing out that <code class="literal">OVER wname</code> is not exactly equivalent to441 <code class="literal">OVER (wname ...)</code>; the latter implies copying and modifying the442 window definition, and will be rejected if the referenced window443 specification includes a frame clause.444 </p><p>445 The <code class="literal">PARTITION BY</code> clause groups the rows of the query into446 <em class="firstterm">partitions</em>, which are processed separately by the window447 function. <code class="literal">PARTITION BY</code> works similarly to a query-level448 <code class="literal">GROUP BY</code> clause, except that its expressions are always just449 expressions and cannot be output-column names or numbers.450 Without <code class="literal">PARTITION BY</code>, all rows produced by the query are451 treated as a single partition.452 The <code class="literal">ORDER BY</code> clause determines the order in which the rows453 of a partition are processed by the window function. It works similarly454 to a query-level <code class="literal">ORDER BY</code> clause, but likewise cannot use455 output-column names or numbers. Without <code class="literal">ORDER BY</code>, rows are456 processed in an unspecified order.457 </p><p>458 The <em class="replaceable"><code>frame_clause</code></em> specifies459 the set of rows constituting the <em class="firstterm">window frame</em>, which is a460 subset of the current partition, for those window functions that act on461 the frame instead of the whole partition. The set of rows in the frame462 can vary depending on which row is the current row. The frame can be463 specified in <code class="literal">RANGE</code>, <code class="literal">ROWS</code>464 or <code class="literal">GROUPS</code> mode; in each case, it runs from465 the <em class="replaceable"><code>frame_start</code></em> to466 the <em class="replaceable"><code>frame_end</code></em>.467 If <em class="replaceable"><code>frame_end</code></em> is omitted, the end defaults468 to <code class="literal">CURRENT ROW</code>.469 </p><p>470 A <em class="replaceable"><code>frame_start</code></em> of <code class="literal">UNBOUNDED PRECEDING</code> means471 that the frame starts with the first row of the partition, and similarly472 a <em class="replaceable"><code>frame_end</code></em> of <code class="literal">UNBOUNDED FOLLOWING</code> means473 that the frame ends with the last row of the partition.474 </p><p>475 In <code class="literal">RANGE</code> or <code class="literal">GROUPS</code> mode,476 a <em class="replaceable"><code>frame_start</code></em> of477 <code class="literal">CURRENT ROW</code> means the frame starts with the current478 row's first <em class="firstterm">peer</em> row (a row that the479 window's <code class="literal">ORDER BY</code> clause sorts as equivalent to the480 current row), while a <em class="replaceable"><code>frame_end</code></em> of481 <code class="literal">CURRENT ROW</code> means the frame ends with the current482 row's last peer row.483 In <code class="literal">ROWS</code> mode, <code class="literal">CURRENT ROW</code> simply484 means the current row.485 </p><p>486 In the <em class="replaceable"><code>offset</code></em> <code class="literal">PRECEDING</code>487 and <em class="replaceable"><code>offset</code></em> <code class="literal">FOLLOWING</code> frame488 options, the <em class="replaceable"><code>offset</code></em> must be an expression not489 containing any variables, aggregate functions, or window functions.490 The meaning of the <em class="replaceable"><code>offset</code></em> depends on the491 frame mode:492 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>493 In <code class="literal">ROWS</code> mode,494 the <em class="replaceable"><code>offset</code></em> must yield a non-null,495 non-negative integer, and the option means that the frame starts or496 ends the specified number of rows before or after the current row.497 </p></li><li class="listitem"><p>498 In <code class="literal">GROUPS</code> mode,499 the <em class="replaceable"><code>offset</code></em> again must yield a non-null,500 non-negative integer, and the option means that the frame starts or501 ends the specified number of <em class="firstterm">peer groups</em>502 before or after the current row's peer group, where a peer group is a503 set of rows that are equivalent in the <code class="literal">ORDER BY</code>504 ordering. (There must be an <code class="literal">ORDER BY</code> clause505 in the window definition to use <code class="literal">GROUPS</code> mode.)506 </p></li><li class="listitem"><p>507 In <code class="literal">RANGE</code> mode, these options require that508 the <code class="literal">ORDER BY</code> clause specify exactly one column.509 The <em class="replaceable"><code>offset</code></em> specifies the maximum510 difference between the value of that column in the current row and511 its value in preceding or following rows of the frame. The data type512 of the <em class="replaceable"><code>offset</code></em> expression varies depending513 on the data type of the ordering column. For numeric ordering514 columns it is typically of the same type as the ordering column,515 but for datetime ordering columns it is an <code class="type">interval</code>.516 For example, if the ordering column is of type <code class="type">date</code>517 or <code class="type">timestamp</code>, one could write <code class="literal">RANGE BETWEEN518 '1 day' PRECEDING AND '10 days' FOLLOWING</code>.519 The <em class="replaceable"><code>offset</code></em> is still required to be520 non-null and non-negative, though the meaning521 of <span class="quote">“<span class="quote">non-negative</span>”</span> depends on its data type.522 </p></li></ul></div><p>523 In any case, the distance to the end of the frame is limited by the524 distance to the end of the partition, so that for rows near the partition525 ends the frame might contain fewer rows than elsewhere.526 </p><p>527 Notice that in both <code class="literal">ROWS</code> and <code class="literal">GROUPS</code>528 mode, <code class="literal">0 PRECEDING</code> and <code class="literal">0 FOLLOWING</code>529 are equivalent to <code class="literal">CURRENT ROW</code>. This normally holds530 in <code class="literal">RANGE</code> mode as well, for an appropriate531 data-type-specific meaning of <span class="quote">“<span class="quote">zero</span>”</span>.532 </p><p>533 The <em class="replaceable"><code>frame_exclusion</code></em> option allows rows around534 the current row to be excluded from the frame, even if they would be535 included according to the frame start and frame end options.536 <code class="literal">EXCLUDE CURRENT ROW</code> excludes the current row from the537 frame.538 <code class="literal">EXCLUDE GROUP</code> excludes the current row and its539 ordering peers from the frame.540 <code class="literal">EXCLUDE TIES</code> excludes any peers of the current541 row from the frame, but not the current row itself.542 <code class="literal">EXCLUDE NO OTHERS</code> simply specifies explicitly the543 default behavior of not excluding the current row or its peers.544 </p><p>545 The default framing option is <code class="literal">RANGE UNBOUNDED PRECEDING</code>,546 which is the same as <code class="literal">RANGE BETWEEN UNBOUNDED PRECEDING AND547 CURRENT ROW</code>. With <code class="literal">ORDER BY</code>, this sets the frame to be548 all rows from the partition start up through the current row's last549 <code class="literal">ORDER BY</code> peer. Without <code class="literal">ORDER BY</code>,550 this means all rows of the partition are included in the window frame,551 since all rows become peers of the current row.552 </p><p>553 Restrictions are that554 <em class="replaceable"><code>frame_start</code></em> cannot be <code class="literal">UNBOUNDED FOLLOWING</code>,555 <em class="replaceable"><code>frame_end</code></em> cannot be <code class="literal">UNBOUNDED PRECEDING</code>,556 and the <em class="replaceable"><code>frame_end</code></em> choice cannot appear earlier in the557 above list of <em class="replaceable"><code>frame_start</code></em>558 and <em class="replaceable"><code>frame_end</code></em> options than559 the <em class="replaceable"><code>frame_start</code></em> choice does — for example560 <code class="literal">RANGE BETWEEN CURRENT ROW AND <em class="replaceable"><code>offset</code></em>561 PRECEDING</code> is not allowed.562 But, for example, <code class="literal">ROWS BETWEEN 7 PRECEDING AND 8563 PRECEDING</code> is allowed, even though it would never select any564 rows.565 </p><p>566 If <code class="literal">FILTER</code> is specified, then only the input567 rows for which the <em class="replaceable"><code>filter_clause</code></em>568 evaluates to true are fed to the window function; other rows569 are discarded. Only window functions that are aggregates accept570 a <code class="literal">FILTER</code> clause.571 </p><p>572 The built-in window functions are described in <a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.64. General-Purpose Window Functions">Table 9.64</a>. Other window functions can be added by573 the user. Also, any built-in or user-defined general-purpose or574 statistical aggregate can be used as a window function. (Ordered-set575 and hypothetical-set aggregates cannot presently be used as window functions.)576 </p><p>577 The syntaxes using <code class="literal">*</code> are used for calling parameter-less578 aggregate functions as window functions, for example579 <code class="literal">count(*) OVER (PARTITION BY x ORDER BY y)</code>.580 The asterisk (<code class="literal">*</code>) is customarily not used for581 window-specific functions. Window-specific functions do not582 allow <code class="literal">DISTINCT</code> or <code class="literal">ORDER BY</code> to be used within the583 function argument list.584 </p><p>585 Window function calls are permitted only in the <code class="literal">SELECT</code>586 list and the <code class="literal">ORDER BY</code> clause of the query.587 </p><p>588 More information about window functions can be found in589 <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a>,590 <a class="xref" href="functions-window.html" title="9.22. Window Functions">Section 9.22</a>, and591 <a class="xref" href="queries-table-expressions.html#QUERIES-WINDOW" title="7.2.5. Window Function Processing">Section 7.2.5</a>.592 </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>593 A type cast specifies a conversion from one data type to another.594 <span class="productname">PostgreSQL</span> accepts two equivalent syntaxes595 for type casts:596</p><pre class="synopsis">597CAST ( <em class="replaceable"><code>expression</code></em> AS <em class="replaceable"><code>type</code></em> )598<em class="replaceable"><code>expression</code></em>::<em class="replaceable"><code>type</code></em>599</pre><p>600 The <code class="literal">CAST</code> syntax conforms to SQL; the syntax with601 <code class="literal">::</code> is historical <span class="productname">PostgreSQL</span>602 usage.603 </p><p>604 When a cast is applied to a value expression of a known type, it605 represents a run-time type conversion. The cast will succeed only606 if a suitable type conversion operation has been defined. Notice that this607 is subtly different from the use of casts with constants, as shown in608 <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 an609 unadorned string literal represents the initial assignment of a type610 to a literal constant value, and so it will succeed for any type611 (if the contents of the string literal are acceptable input syntax for the612 data type).613 </p><p>614 An explicit type cast can usually be omitted if there is no ambiguity as615 to the type that a value expression must produce (for example, when it is616 assigned to a table column); the system will automatically apply a617 type cast in such cases. However, automatic casting is only done for618 casts that are marked <span class="quote">“<span class="quote">OK to apply implicitly</span>”</span>619 in the system catalogs. Other casts must be invoked with620 explicit casting syntax. This restriction is intended to prevent621 surprising conversions from being applied silently.622 </p><p>623 It is also possible to specify a type cast using a function-like624 syntax:625</p><pre class="synopsis">626<em class="replaceable"><code>typename</code></em> ( <em class="replaceable"><code>expression</code></em> )627</pre><p>628 However, this only works for types whose names are also valid as629 function names. For example, <code class="literal">double precision</code>630 cannot be used this way, but the equivalent <code class="literal">float8</code>631 can. Also, the names <code class="literal">interval</code>, <code class="literal">time</code>, and632 <code class="literal">timestamp</code> can only be used in this fashion if they are633 double-quoted, because of syntactic conflicts. Therefore, the use of634 the function-like cast syntax leads to inconsistencies and should635 probably be avoided.636 </p><div class="note"><h3 class="title">Note</h3><p>637 The function-like syntax is in fact just a function call. When638 one of the two standard cast syntaxes is used to do a run-time639 conversion, it will internally invoke a registered function to640 perform the conversion. By convention, these conversion functions641 have the same name as their output type, and thus the <span class="quote">“<span class="quote">function-like642 syntax</span>”</span> is nothing more than a direct invocation of the underlying643 conversion function. Obviously, this is not something that a portable644 application should rely on. For further details see645 <a class="xref" href="sql-createcast.html" title="CREATE CAST"><span class="refentrytitle">CREATE CAST</span></a>.646 </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>647 The <code class="literal">COLLATE</code> clause overrides the collation of648 an expression. It is appended to the expression it applies to:649</p><pre class="synopsis">650<em class="replaceable"><code>expr</code></em> COLLATE <em class="replaceable"><code>collation</code></em>651</pre><p>652 where <em class="replaceable"><code>collation</code></em> is a possibly653 schema-qualified identifier. The <code class="literal">COLLATE</code>654 clause binds tighter than operators; parentheses can be used when655 necessary.656 </p><p>657 If no collation is explicitly specified, the database system658 either derives a collation from the columns involved in the659 expression, or it defaults to the default collation of the660 database if no column is involved in the expression.661 </p><p>662 The two common uses of the <code class="literal">COLLATE</code> clause are663 overriding the sort order in an <code class="literal">ORDER BY</code> clause, for664 example:665</p><pre class="programlisting">666SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";667</pre><p>668 and overriding the collation of a function or operator call that669 has locale-sensitive results, for example:670</p><pre class="programlisting">671SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";672</pre><p>673 Note that in the latter case the <code class="literal">COLLATE</code> clause is674 attached to an input argument of the operator we wish to affect.675 It doesn't matter which argument of the operator or function call the676 <code class="literal">COLLATE</code> clause is attached to, because the collation that is677 applied by the operator or function is derived by considering all678 arguments, and an explicit <code class="literal">COLLATE</code> clause will override the679 collations of all other arguments. (Attaching non-matching680 <code class="literal">COLLATE</code> clauses to more than one argument, however, is an681 error. For more details see <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a>.)682 Thus, this gives the same result as the previous example:683</p><pre class="programlisting">684SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';685</pre><p>686 But this is an error:687</p><pre class="programlisting">688SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";689</pre><p>690 because it attempts to apply a collation to the result of the691 <code class="literal">></code> operator, which is of the non-collatable data type692 <code class="type">boolean</code>.693 </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>694 A scalar subquery is an ordinary695 <code class="command">SELECT</code> query in parentheses that returns exactly one696 row with one column. (See <a class="xref" href="queries.html" title="Chapter 7. Queries">Chapter 7</a> for information about writing queries.)697 The <code class="command">SELECT</code> query is executed698 and the single returned value is used in the surrounding value expression.699 It is an error to use a query that700 returns more than one row or more than one column as a scalar subquery.701 (But if, during a particular execution, the subquery returns no rows,702 there is no error; the scalar result is taken to be null.)703 The subquery can refer to variables from the surrounding query,704 which will act as constants during any one evaluation of the subquery.705 See also <a class="xref" href="functions-subquery.html" title="9.23. Subquery Expressions">Section 9.23</a> for other expressions involving subqueries.706 </p><p>707 For example, the following finds the largest city population in each708 state:709</p><pre class="programlisting">710SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)711 FROM states;712</pre><p>713 </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>714 An array constructor is an expression that builds an715 array value using values for its member elements. A simple array716 constructor717 consists of the key word <code class="literal">ARRAY</code>, a left square bracket718 <code class="literal">[</code>, a list of expressions (separated by commas) for the719 array element values, and finally a right square bracket <code class="literal">]</code>.720 For example:721</p><pre class="programlisting">722SELECT ARRAY[1,2,3+4];723 array724---------725 {1,2,7}726(1 row)727</pre><p>728 By default,729 the array element type is the common type of the member expressions,730 determined using the same rules as for <code class="literal">UNION</code> or731 <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>).732 You can override this by explicitly casting the array constructor to the733 desired type, for example:734</p><pre class="programlisting">735SELECT ARRAY[1,2,22.7]::integer[];736 array737----------738 {1,2,23}739(1 row)740</pre><p>741 This has the same effect as casting each expression to the array742 element type individually.743 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>.744 </p><p>745 Multidimensional array values can be built by nesting array746 constructors.747 In the inner constructors, the key word <code class="literal">ARRAY</code> can748 be omitted. For example, these produce the same result:749 750</p><pre class="programlisting">751SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];752 array753---------------754 {{1,2},{3,4}}755(1 row)756 757SELECT ARRAY[[1,2],[3,4]];758 array759---------------760 {{1,2},{3,4}}761(1 row)762</pre><p>763 764 Since multidimensional arrays must be rectangular, inner constructors765 at the same level must produce sub-arrays of identical dimensions.766 Any cast applied to the outer <code class="literal">ARRAY</code> constructor propagates767 automatically to all the inner constructors.768 </p><p>769 Multidimensional array constructor elements can be anything yielding770 an array of the proper kind, not only a sub-<code class="literal">ARRAY</code> construct.771 For example:772</p><pre class="programlisting">773CREATE TABLE arr(f1 int[], f2 int[]);774 775INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);776 777SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;778 array779------------------------------------------------780 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}781(1 row)782</pre><p>783 </p><p>784 You can construct an empty array, but since it's impossible to have an785 array with no type, you must explicitly cast your empty array to the786 desired type. For example:787</p><pre class="programlisting">788SELECT ARRAY[]::integer[];789 array790-------791 {}792(1 row)793</pre><p>794 </p><p>795 It is also possible to construct an array from the results of a796 subquery. In this form, the array constructor is written with the797 key word <code class="literal">ARRAY</code> followed by a parenthesized (not798 bracketed) subquery. For example:799</p><pre class="programlisting">800SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');801 array802------------------------------------------------------------------803 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412}804(1 row)805 806SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));807 array808----------------------------------809 {{1,2},{2,4},{3,6},{4,8},{5,10}}810(1 row)811</pre><p>812 The subquery must return a single column.813 If the subquery's output column is of a non-array type, the resulting814 one-dimensional array will have an element for each row in the815 subquery result, with an element type matching that of the816 subquery's output column.817 If the subquery's output column is of an array type, the result will be818 an array of the same type but one higher dimension; in this case all819 the subquery rows must yield arrays of identical dimensionality, else820 the result would not be rectangular.821 </p><p>822 The subscripts of an array value built with <code class="literal">ARRAY</code>823 always begin with one. For more information about arrays, see824 <a class="xref" href="arrays.html" title="8.15. Arrays">Section 8.15</a>.825 </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>826 A row constructor is an expression that builds a row value (also827 called a composite value) using values828 for its member fields. A row constructor consists of the key word829 <code class="literal">ROW</code>, a left parenthesis, zero or more830 expressions (separated by commas) for the row field values, and finally831 a right parenthesis. For example:832</p><pre class="programlisting">833SELECT ROW(1,2.5,'this is a test');834</pre><p>835 The key word <code class="literal">ROW</code> is optional when there is more than one836 expression in the list.837 </p><p>838 A row constructor can include the syntax839 <em class="replaceable"><code>rowvalue</code></em><code class="literal">.*</code>,840 which will be expanded to a list of the elements of the row value,841 just as occurs when the <code class="literal">.*</code> syntax is used at the top level842 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>).843 For example, if table <code class="literal">t</code> has844 columns <code class="literal">f1</code> and <code class="literal">f2</code>, these are the same:845</p><pre class="programlisting">846SELECT ROW(t.*, 42) FROM t;847SELECT ROW(t.f1, t.f2, 42) FROM t;848</pre><p>849 </p><div class="note"><h3 class="title">Note</h3><p>850 Before <span class="productname">PostgreSQL</span> 8.2, the851 <code class="literal">.*</code> syntax was not expanded in row constructors, so852 that writing <code class="literal">ROW(t.*, 42)</code> created a two-field row whose first853 field was another row value. The new behavior is usually more useful.854 If you need the old behavior of nested row values, write the inner855 row value without <code class="literal">.*</code>, for instance856 <code class="literal">ROW(t, 42)</code>.857 </p></div><p>858 By default, the value created by a <code class="literal">ROW</code> expression is of859 an anonymous record type. If necessary, it can be cast to a named860 composite type — either the row type of a table, or a composite type861 created with <code class="command">CREATE TYPE AS</code>. An explicit cast might be needed862 to avoid ambiguity. For example:863</p><pre class="programlisting">864CREATE TABLE mytable(f1 int, f2 float, f3 text);865 866CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;867 868-- No cast needed since only one getf1() exists869SELECT getf1(ROW(1,2.5,'this is a test'));870 getf1871-------872 1873(1 row)874 875CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);876 877CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;878 879-- Now we need a cast to indicate which function to call:880SELECT getf1(ROW(1,2.5,'this is a test'));881ERROR: function getf1(record) is not unique882 883SELECT getf1(ROW(1,2.5,'this is a test')::mytable);884 getf1885-------886 1887(1 row)888 889SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));890 getf1891-------892 11893(1 row)894</pre><p>895 </p><p>896 Row constructors can be used to build composite values to be stored897 in a composite-type table column, or to be passed to a function that898 accepts a composite parameter. Also,899 it is possible to compare two row values or test a row with900 <code class="literal">IS NULL</code> or <code class="literal">IS NOT NULL</code>, for example:901</p><pre class="programlisting">902SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');903 904SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows905</pre><p>906 For more detail see <a class="xref" href="functions-comparisons.html" title="9.24. Row and Array Comparisons">Section 9.24</a>.907 Row constructors can also be used in connection with subqueries,908 as discussed in <a class="xref" href="functions-subquery.html" title="9.23. Subquery Expressions">Section 9.23</a>.909 </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>910 The order of evaluation of subexpressions is not defined. In911 particular, the inputs of an operator or function are not necessarily912 evaluated left-to-right or in any other fixed order.913 </p><p>914 Furthermore, if the result of an expression can be determined by915 evaluating only some parts of it, then other subexpressions916 might not be evaluated at all. For instance, if one wrote:917</p><pre class="programlisting">918SELECT true OR somefunc();919</pre><p>920 then <code class="literal">somefunc()</code> would (probably) not be called921 at all. The same would be the case if one wrote:922</p><pre class="programlisting">923SELECT somefunc() OR true;924</pre><p>925 Note that this is not the same as the left-to-right926 <span class="quote">“<span class="quote">short-circuiting</span>”</span> of Boolean operators that is found927 in some programming languages.928 </p><p>929 As a consequence, it is unwise to use functions with side effects930 as part of complex expressions. It is particularly dangerous to931 rely on side effects or evaluation order in <code class="literal">WHERE</code> and <code class="literal">HAVING</code> clauses,932 since those clauses are extensively reprocessed as part of933 developing an execution plan. Boolean934 expressions (<code class="literal">AND</code>/<code class="literal">OR</code>/<code class="literal">NOT</code> combinations) in those clauses can be reorganized935 in any manner allowed by the laws of Boolean algebra.936 </p><p>937 When it is essential to force evaluation order, a <code class="literal">CASE</code>938 construct (see <a class="xref" href="functions-conditional.html" title="9.18. Conditional Expressions">Section 9.18</a>) can be939 used. For example, this is an untrustworthy way of trying to940 avoid division by zero in a <code class="literal">WHERE</code> clause:941</p><pre class="programlisting">942SELECT ... WHERE x > 0 AND y/x > 1.5;943</pre><p>944 But this is safe:945</p><pre class="programlisting">946SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;947</pre><p>948 A <code class="literal">CASE</code> construct used in this fashion will defeat optimization949 attempts, so it should only be done when necessary. (In this particular950 example, it would be better to sidestep the problem by writing951 <code class="literal">y > 1.5*x</code> instead.)952 </p><p>953 <code class="literal">CASE</code> is not a cure-all for such issues, however.954 One limitation of the technique illustrated above is that it does not955 prevent early evaluation of constant subexpressions.956 As described in <a class="xref" href="xfunc-volatility.html" title="38.7. Function Volatility Categories">Section 38.7</a>, functions and957 operators marked <code class="literal">IMMUTABLE</code> can be evaluated when958 the query is planned rather than when it is executed. Thus for example959</p><pre class="programlisting">960SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;961</pre><p>962 is likely to result in a division-by-zero failure due to the planner963 trying to simplify the constant subexpression,964 even if every row in the table has <code class="literal">x > 0</code> so that the965 <code class="literal">ELSE</code> arm would never be entered at run time.966 </p><p>967 While that particular example might seem silly, related cases that don't968 obviously involve constants can occur in queries executed within969 functions, since the values of function arguments and local variables970 can be inserted into queries as constants for planning purposes.971 Within <span class="application">PL/pgSQL</span> functions, for example, using an972 <code class="literal">IF</code>-<code class="literal">THEN</code>-<code class="literal">ELSE</code> statement to protect973 a risky computation is much safer than just nesting it in a974 <code class="literal">CASE</code> expression.975 </p><p>976 Another limitation of the same kind is that a <code class="literal">CASE</code> cannot977 prevent evaluation of an aggregate expression contained within it,978 because aggregate expressions are computed before other979 expressions in a <code class="literal">SELECT</code> list or <code class="literal">HAVING</code> clause980 are considered. For example, the following query can cause a981 division-by-zero error despite seemingly having protected against it:982</p><pre class="programlisting">983SELECT CASE WHEN min(employees) > 0984 THEN avg(expenses / employees)985 END986 FROM departments;987</pre><p>988 The <code class="function">min()</code> and <code class="function">avg()</code> aggregates are computed989 concurrently over all the input rows, so if any row990 has <code class="structfield">employees</code> equal to zero, the division-by-zero error991 will occur before there is any opportunity to test the result of992 <code class="function">min()</code>. Instead, use a <code class="literal">WHERE</code>993 or <code class="literal">FILTER</code> clause to prevent problematic input rows from994 reaching an aggregate function in the first place.995 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 4.3. Calling Functions</td></tr></table></div></body></html>