Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-expressions.html995 linesDownload Raw Back to html
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 &lt; 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 &gt; '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" &gt; 'foo';685</pre><p>686    But this is an error:687</p><pre class="programlisting">688SELECT * FROM tbl WHERE (a &gt; 'foo') COLLATE "C";689</pre><p>690    because it attempts to apply a collation to the result of the691    <code class="literal">&gt;</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 &gt; 0 AND y/x &gt; 1.5;943</pre><p>944    But this is safe:945</p><pre class="programlisting">946SELECT ... WHERE CASE WHEN x &gt; 0 THEN y/x &gt; 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 &gt; 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 &gt; 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 &gt; 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) &gt; 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>
codekingpro/portable-devtools · Team Ai