2 $Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.82 2003/08/14 23:13:27 tgl Exp $
5 <chapter id="sql-syntax">
6 <title>SQL Syntax</title>
8 <indexterm zone="sql-syntax">
9 <primary>syntax</primary>
10 <secondary>SQL</secondary>
14 This chapter describes the syntax of SQL. It forms the foundation
15 for understanding the following chapters which will go into detail
16 about how the SQL commands are applied to define and modify data.
20 We also advise users who are already familiar with SQL to read this
21 chapter carefully because there are several rules and concepts that
22 are implemented inconsistently among SQL databases or that are
23 specific to <productname>PostgreSQL</productname>.
26 <sect1 id="sql-syntax-lexical">
27 <title>Lexical Structure</title>
30 SQL input consists of a sequence of
31 <firstterm>commands</firstterm>. A command is composed of a
32 sequence of <firstterm>tokens</firstterm>, terminated by a
33 semicolon (<quote>;</quote>). The end of the input stream also
34 terminates a command. Which tokens are valid depends on the syntax
35 of the particular command.
39 A token can be a <firstterm>key word</firstterm>, an
40 <firstterm>identifier</firstterm>, a <firstterm>quoted
41 identifier</firstterm>, a <firstterm>literal</firstterm> (or
42 constant), or a special character symbol. Tokens are normally
43 separated by whitespace (space, tab, newline), but need not be if
44 there is no ambiguity (which is generally only the case if a
45 special character is adjacent to some other token type).
49 Additionally, <firstterm>comments</firstterm> can occur in SQL
50 input. They are not tokens, they are effectively equivalent to
55 For example, the following is (syntactically) valid SQL input:
57 SELECT * FROM MY_TABLE;
58 UPDATE MY_TABLE SET A = 5;
59 INSERT INTO MY_TABLE VALUES (3, 'hi there');
61 This is a sequence of three commands, one per line (although this
62 is not required; more than one command can be on a line, and
63 commands can usefully be split across lines).
67 The SQL syntax is not very consistent regarding what tokens
68 identify commands and which are operands or parameters. The first
69 few tokens are generally the command name, so in the above example
70 we would usually speak of a <quote>SELECT</quote>, an
71 <quote>UPDATE</quote>, and an <quote>INSERT</quote> command. But
72 for instance the <command>UPDATE</command> command always requires
73 a <token>SET</token> token to appear in a certain position, and
74 this particular variation of <command>INSERT</command> also
75 requires a <token>VALUES</token> in order to be complete. The
76 precise syntax rules for each command are described in <xref linkend="reference">.
79 <sect2 id="sql-syntax-identifiers">
80 <title>Identifiers and Key Words</title>
82 <indexterm zone="sql-syntax-identifiers">
83 <primary>identifiers</primary>
86 <indexterm zone="sql-syntax-identifiers">
87 <primary>key words</primary>
88 <secondary>syntax</secondary>
92 Tokens such as <token>SELECT</token>, <token>UPDATE</token>, or
93 <token>VALUES</token> in the example above are examples of
94 <firstterm>key words</firstterm>, that is, words that have a fixed
95 meaning in the SQL language. The tokens <token>MY_TABLE</token>
96 and <token>A</token> are examples of
97 <firstterm>identifiers</firstterm>. They identify names of
98 tables, columns, or other database objects, depending on the
99 command they are used in. Therefore they are sometimes simply
100 called <quote>names</quote>. Key words and identifiers have the
101 same lexical structure, meaning that one cannot know whether a
102 token is an identifier or a key word without knowing the language.
103 A complete list of key words can be found in <xref
104 linkend="sql-keywords-appendix">.
108 SQL identifiers and key words must begin with a letter
109 (<literal>a</literal>-<literal>z</literal>, but also letters with
110 diacritical marks and non-Latin letters) or an underscore
111 (<literal>_</literal>). Subsequent characters in an identifier or
112 key word can be letters, underscores, digits
113 (<literal>0</literal>-<literal>9</literal>), or dollar signs
114 (<literal>$</>). Note that dollar signs are not allowed in identifiers
115 according to the letter of the SQL standard, so their use may render
116 applications less portable.
117 The SQL standard will not define a key word that contains
118 digits or starts or ends with an underscore, so identifiers of this
119 form are safe against possible conflict with future extensions of the
124 The system uses no more than <symbol>NAMEDATALEN</symbol>-1
125 characters of an identifier; longer names can be written in
126 commands, but they will be truncated. By default,
127 <symbol>NAMEDATALEN</symbol> is 64 so the maximum identifier
128 length is 63. If this limit is problematic, it can be raised by
129 changing the <symbol>NAMEDATALEN</symbol> constant in
130 <filename>src/include/postgres_ext.h</filename>.
135 <primary>case sensitivity</primary>
136 <secondary>SQL commands</secondary>
138 Identifier and key word names are case insensitive. Therefore
140 UPDATE MY_TABLE SET A = 5;
142 can equivalently be written as
144 uPDaTE my_TabLE SeT a = 5;
146 A convention often used is to write key words in upper
147 case and names in lower case, e.g.,
149 UPDATE my_table SET a = 5;
155 <primary>quotes</primary>
156 <secondary>and identifiers</secondary>
158 There is a second kind of identifier: the <firstterm>delimited
159 identifier</firstterm> or <firstterm>quoted
160 identifier</firstterm>. It is formed by enclosing an arbitrary
161 sequence of characters in double-quotes
162 (<literal>"</literal>). <!-- " font-lock mania --> A delimited
163 identifier is always an identifier, never a key word. So
164 <literal>"select"</literal> could be used to refer to a column or
165 table named <quote>select</quote>, whereas an unquoted
166 <literal>select</literal> would be taken as a key word and
167 would therefore provoke a parse error when used where a table or
168 column name is expected. The example can be written with quoted
169 identifiers like this:
171 UPDATE "my_table" SET "a" = 5;
176 Quoted identifiers can contain any character other than a double
177 quote itself. To include a double quote, write two double quotes.
178 This allows constructing table or column names that would
179 otherwise not be possible, such as ones containing spaces or
180 ampersands. The length limitation still applies.
184 Quoting an identifier also makes it case-sensitive, whereas
185 unquoted names are always folded to lower case. For example, the
186 identifiers <literal>FOO</literal>, <literal>foo</literal>, and
187 <literal>"foo"</literal> are considered the same by
188 <productname>PostgreSQL</productname>, but <literal>"Foo"</literal>
189 and <literal>"FOO"</literal> are different from these three and
193 The folding of unquoted names to lower case in <productname>PostgreSQL</>
194 is incompatible with the SQL standard, which says that unquoted
195 names should be folded to upper case. Thus, <literal>foo</literal>
196 should be equivalent to <literal>"FOO"</literal> not
197 <literal>"foo"</literal> according to the standard. If you want to
198 write portable applications you are advised to always quote a particular
199 name or never quote it.
206 <sect2 id="sql-syntax-constants">
207 <title>Constants</title>
209 <indexterm zone="sql-syntax-constants">
210 <primary>constants</primary>
214 There are three kinds of <firstterm>implicitly-typed
215 constants</firstterm> in <productname>PostgreSQL</productname>:
216 strings, bit strings, and numbers.
217 Constants can also be specified with explicit types, which can
218 enable more accurate representation and more efficient handling by
219 the system. The implicit constants are described below; explicit
220 constants are discussed afterwards.
223 <sect3 id="sql-syntax-strings">
224 <title>String Constants</title>
226 <indexterm zone="sql-syntax-strings">
227 <primary>character strings</primary>
228 <secondary>constants</secondary>
233 <primary>quotes</primary>
234 <secondary>escaping</secondary>
236 A string constant in SQL is an arbitrary sequence of characters
237 bounded by single quotes (<quote>'</quote>), e.g., <literal>'This
238 is a string'</literal>. SQL allows single quotes to be embedded
239 in strings by typing two adjacent single quotes (e.g.,
240 <literal>'Dianne''s horse'</literal>). In
241 <productname>PostgreSQL</productname> single quotes may
242 alternatively be escaped with a backslash (<quote>\</quote>,
243 e.g., <literal>'Dianne\'s horse'</literal>).
247 C-style backslash escapes are also available:
248 <literal>\b</literal> is a backspace, <literal>\f</literal> is a
249 form feed, <literal>\n</literal> is a newline,
250 <literal>\r</literal> is a carriage return, <literal>\t</literal>
251 is a tab, and <literal>\<replaceable>xxx</replaceable></literal>,
252 where <replaceable>xxx</replaceable> is an octal number, is the
253 character with the corresponding ASCII code. Any other character
254 following a backslash is taken literally. Thus, to include a
255 backslash in a string constant, type two backslashes.
259 The character with the code zero cannot be in a string constant.
263 Two string constants that are only separated by whitespace
264 <emphasis>with at least one newline</emphasis> are concatenated
265 and effectively treated as if the string had been written in one
266 constant. For example:
279 is not valid syntax. (This slightly bizarre behavior is specified
280 by <acronym>SQL</acronym>; <productname>PostgreSQL</productname> is
281 following the standard.)
285 <sect3 id="sql-syntax-bit-strings">
286 <title>Bit-String Constants</title>
288 <indexterm zone="sql-syntax-bit-strings">
289 <primary>bit strings</primary>
290 <secondary>constants</secondary>
294 Bit-string constants look like string constants with a
295 <literal>B</literal> (upper or lower case) immediately before the
296 opening quote (no intervening whitespace), e.g.,
297 <literal>B'1001'</literal>. The only characters allowed within
298 bit-string constants are <literal>0</literal> and
299 <literal>1</literal>.
303 Alternatively, bit-string constants can be specified in hexadecimal
304 notation, using a leading <literal>X</literal> (upper or lower case),
305 e.g., <literal>X'1FF'</literal>. This notation is equivalent to
306 a bit-string constant with four binary digits for each hexadecimal digit.
310 Both forms of bit-string constant can be continued
311 across lines in the same way as regular string constants.
316 <title>Numeric Constants</title>
319 <primary>numeric</primary>
320 <secondary>constants</secondary>
324 Numeric constants are accepted in these general forms:
326 <replaceable>digits</replaceable>
327 <replaceable>digits</replaceable>.<optional><replaceable>digits</replaceable></optional><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
328 <optional><replaceable>digits</replaceable></optional>.<replaceable>digits</replaceable><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
329 <replaceable>digits</replaceable>e<optional>+-</optional><replaceable>digits</replaceable>
331 where <replaceable>digits</replaceable> is one or more decimal
332 digits (0 through 9). At least one digit must be before or after the
333 decimal point, if one is used. At least one digit must follow the
334 exponent marker (<literal>e</literal>), if one is present.
335 There may not be any spaces or other characters embedded in the
336 constant. Note that any leading plus or minus sign is not actually
337 considered part of the constant; it is an operator applied to the
342 These are some examples of valid numeric constants:
354 A numeric constant that contains neither a decimal point nor an
355 exponent is initially presumed to be type <type>integer</> if its
356 value fits in type <type>integer</> (32 bits); otherwise it is
357 presumed to be type <type>bigint</> if its
358 value fits in type <type>bigint</> (64 bits); otherwise it is
359 taken to be type <type>numeric</>. Constants that contain decimal
360 points and/or exponents are always initially presumed to be type
365 The initially assigned data type of a numeric constant is just a
366 starting point for the type resolution algorithms. In most
367 cases the constant will be automatically coerced to the most
368 appropriate type depending on context. When necessary, you
369 can force a numeric value to be interpreted as a specific
370 data type by casting it. For example, you can force a numeric
371 value to be treated as type <type>real</> (<type>float4</>)
375 REAL '1.23' -- string style
376 1.23::REAL -- PostgreSQL (historical) style
381 <sect3 id="sql-syntax-constants-generic">
382 <title>Constants of Other Types</title>
385 <primary>data types</primary>
386 <secondary>constants</secondary>
390 A constant of an <emphasis>arbitrary</emphasis> type can be
391 entered using any one of the following notations:
393 <replaceable>type</replaceable> '<replaceable>string</replaceable>'
394 '<replaceable>string</replaceable>'::<replaceable>type</replaceable>
395 CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
397 The string's text is passed to the input conversion
398 routine for the type called <replaceable>type</replaceable>. The
399 result is a constant of the indicated type. The explicit type
400 cast may be omitted if there is no ambiguity as to the type the
401 constant must be (for example, when it is passed as an argument
402 to a non-overloaded function), in which case it is automatically
407 It is also possible to specify a type coercion using a function-like
410 <replaceable>typename</replaceable> ( '<replaceable>string</replaceable>' )
412 but not all type names may be used in this way; see <xref
413 linkend="sql-syntax-type-casts"> for details.
417 The <literal>::</literal>, <literal>CAST()</literal>, and
418 function-call syntaxes can also be used to specify run-time type
419 conversions of arbitrary expressions, as discussed in <xref
420 linkend="sql-syntax-type-casts">. But the form
421 <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal>
422 can only be used to specify the type of a literal constant.
423 Another restriction on
424 <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal>
425 is that it does not work for array types; use <literal>::</literal>
426 or <literal>CAST()</literal> to specify the type of an array constant.
431 <title>Array constants</title>
434 <primary>arrays</primary>
435 <secondary>constants</secondary>
439 The general format of an array constant is the following:
441 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
443 where <replaceable>delim</replaceable> is the delimiter character
444 for the type, as recorded in its <literal>pg_type</literal>
445 entry. (For all built-in types, this is the comma character
446 <quote><literal>,</literal></>.) Each <replaceable>val</replaceable> is either a constant
447 of the array element type, or a subarray. An example of an
450 '{{1,2,3},{4,5,6},{7,8,9}}'
452 This constant is a two-dimensional, 3-by-3 array consisting of three
453 subarrays of integers. For more information see <xref linkend="arrays">.
457 (Array constants are actually only a special case of the generic
458 type constants discussed in the previous section. The constant
459 is initially treated as a string and passed to the array input
460 conversion routine. An explicit type specification might be
467 <sect2 id="sql-syntax-operators">
468 <title>Operators</title>
470 <indexterm zone="sql-syntax-operators">
471 <primary>operators</primary>
472 <secondary>syntax</secondary>
476 An operator is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
477 (63 by default) characters from the following list:
479 + - * / < > = ~ ! @ # % ^ & | ` ?
482 There are a few restrictions on operator names, however:
486 <literal>--</literal> and <literal>/*</literal> cannot appear
487 anywhere in an operator name, since they will be taken as the
494 A multiple-character operator name cannot end in <literal>+</> or <literal>-</>,
495 unless the name also contains at least one of these characters:
497 ~ ! @ # % ^ & | ` ?
499 For example, <literal>@-</literal> is an allowed operator name,
500 but <literal>*-</literal> is not. This restriction allows
501 <productname>PostgreSQL</productname> to parse SQL-compliant
502 queries without requiring spaces between tokens.
509 When working with non-SQL-standard operator names, you will usually
510 need to separate adjacent operators with spaces to avoid ambiguity.
511 For example, if you have defined a left unary operator named <literal>@</literal>,
512 you cannot write <literal>X*@Y</literal>; you must write
513 <literal>X* @Y</literal> to ensure that
514 <productname>PostgreSQL</productname> reads it as two operator names
520 <title>Special Characters</title>
523 Some characters that are not alphanumeric have a special meaning
524 that is different from being an operator. Details on the usage can
525 be found at the location where the respective syntax element is
526 described. This section only exists to advise the existence and
527 summarize the purposes of these characters.
532 A dollar sign (<literal>$</literal>) followed by digits is used
533 to represent a positional parameter in the body of a function
534 definition or a prepared statement. In other contexts the
535 dollar sign may be part of an identifier.
541 Parentheses (<literal>()</literal>) have their usual meaning to
542 group expressions and enforce precedence. In some cases
543 parentheses are required as part of the fixed syntax of a
544 particular SQL command.
550 Brackets (<literal>[]</literal>) are used to select the elements
551 of an array. See <xref linkend="arrays"> for more information
558 Commas (<literal>,</literal>) are used in some syntactical
559 constructs to separate the elements of a list.
565 The semicolon (<literal>;</literal>) terminates an SQL command.
566 It cannot appear anywhere within a command, except within a
567 string constant or quoted identifier.
573 The colon (<literal>:</literal>) is used to select
574 <quote>slices</quote> from arrays. (See <xref
575 linkend="arrays">.) In certain SQL dialects (such as Embedded
576 SQL), the colon is used to prefix variable names.
582 The asterisk (<literal>*</literal>) has a special meaning when
583 used in the <command>SELECT</command> command or with the
584 <function>COUNT</function> aggregate function.
590 The period (<literal>.</literal>) is used in numeric
591 constants, and to separate schema, table, and column names.
599 <sect2 id="sql-syntax-comments">
600 <title>Comments</title>
602 <indexterm zone="sql-syntax-comments">
603 <primary>comments</primary>
604 <secondary>in SQL</secondary>
608 A comment is an arbitrary sequence of characters beginning with
609 double dashes and extending to the end of the line, e.g.:
611 -- This is a standard SQL92 comment
616 Alternatively, C-style block comments can be used:
619 * with nesting: /* nested block comment */
622 where the comment begins with <literal>/*</literal> and extends to
623 the matching occurrence of <literal>*/</literal>. These block
624 comments nest, as specified in SQL99 but unlike C, so that one can
625 comment out larger blocks of code that may contain existing block
630 A comment is removed from the input stream before further syntax
631 analysis and is effectively replaced by whitespace.
635 <sect2 id="sql-precedence">
636 <title>Lexical Precedence</title>
638 <indexterm zone="sql-precedence">
639 <primary>operators</primary>
640 <secondary>precedence</secondary>
644 <xref linkend="sql-precedence-table"> shows the precedence and
645 associativity of the operators in <productname>PostgreSQL</>.
646 Most operators have the same precedence and are left-associative.
647 The precedence and associativity of the operators is hard-wired
648 into the parser. This may lead to non-intuitive behavior; for
649 example the Boolean operators <literal><</> and
650 <literal>></> have a different precedence than the Boolean
651 operators <literal><=</> and <literal>>=</>. Also, you will
652 sometimes need to add parentheses when using combinations of
653 binary and unary operators. For instance
661 because the parser has no idea -- until it is too late -- that
662 <token>!</token> is defined as a postfix operator, not an infix one.
663 To get the desired behavior in this case, you must write
667 This is the price one pays for extensibility.
670 <table id="sql-precedence-table">
671 <title>Operator Precedence (decreasing)</title>
676 <entry>Operator/Element</entry>
677 <entry>Associativity</entry>
678 <entry>Description</entry>
684 <entry><token>.</token></entry>
686 <entry>table/column name separator</entry>
690 <entry><token>::</token></entry>
692 <entry><productname>PostgreSQL</productname>-style typecast</entry>
696 <entry><token>[</token> <token>]</token></entry>
698 <entry>array element selection</entry>
702 <entry><token>-</token></entry>
704 <entry>unary minus</entry>
708 <entry><token>^</token></entry>
710 <entry>exponentiation</entry>
714 <entry><token>*</token> <token>/</token> <token>%</token></entry>
716 <entry>multiplication, division, modulo</entry>
720 <entry><token>+</token> <token>-</token></entry>
722 <entry>addition, subtraction</entry>
726 <entry><token>IS</token></entry>
728 <entry><literal>IS TRUE</>, <literal>IS FALSE</>, <literal>IS UNKNOWN</>, <literal>IS NULL</></entry>
732 <entry><token>ISNULL</token></entry>
734 <entry>test for null</entry>
738 <entry><token>NOTNULL</token></entry>
740 <entry>test for not null</entry>
744 <entry>(any other)</entry>
746 <entry>all other native and user-defined operators</entry>
750 <entry><token>IN</token></entry>
752 <entry>set membership</entry>
756 <entry><token>BETWEEN</token></entry>
758 <entry>containment</entry>
762 <entry><token>OVERLAPS</token></entry>
764 <entry>time interval overlap</entry>
768 <entry><token>LIKE</token> <token>ILIKE</token> <token>SIMILAR</token></entry>
770 <entry>string pattern matching</entry>
774 <entry><token><</token> <token>></token></entry>
776 <entry>less than, greater than</entry>
780 <entry><token>=</token></entry>
782 <entry>equality, assignment</entry>
786 <entry><token>NOT</token></entry>
788 <entry>logical negation</entry>
792 <entry><token>AND</token></entry>
794 <entry>logical conjunction</entry>
798 <entry><token>OR</token></entry>
800 <entry>logical disjunction</entry>
807 Note that the operator precedence rules also apply to user-defined
808 operators that have the same names as the built-in operators
809 mentioned above. For example, if you define a
810 <quote>+</quote> operator for some custom data type it will have
811 the same precedence as the built-in <quote>+</quote> operator, no
812 matter what yours does.
816 When a schema-qualified operator name is used in the
817 <literal>OPERATOR</> syntax, as for example in
819 SELECT 3 OPERATOR(pg_catalog.+) 4;
821 the <literal>OPERATOR</> construct is taken to have the default precedence
822 shown in <xref linkend="sql-precedence-table"> for <quote>any other</> operator. This is true no matter
823 which specific operator name appears inside <literal>OPERATOR()</>.
828 <sect1 id="sql-expressions">
829 <title>Value Expressions</title>
832 Value expressions are used in a variety of contexts, such
833 as in the target list of the <command>SELECT</command> command, as
834 new column values in <command>INSERT</command> or
835 <command>UPDATE</command>, or in search conditions in a number of
836 commands. The result of a value expression is sometimes called a
837 <firstterm>scalar</firstterm>, to distinguish it from the result of
838 a table expression (which is a table). Value expressions are
839 therefore also called <firstterm>scalar expressions</firstterm> (or
840 even simply <firstterm>expressions</firstterm>). The expression
841 syntax allows the calculation of values from primitive parts using
842 arithmetic, logical, set, and other operations.
846 A value expression is one of the following:
851 A constant or literal value; see <xref linkend="sql-syntax-constants">.
863 A positional parameter reference, in the body of a function definition
864 or prepared statement.
870 An operator invocation.
882 An aggregate expression.
900 An array constructor.
906 Another value expression in parentheses, useful to group subexpressions and override precedence.
913 In addition to this list, there are a number of constructs that can
914 be classified as an expression but do not follow any general syntax
915 rules. These generally have the semantics of a function or
916 operator and are explained in the appropriate location in <xref
917 linkend="functions">. An example is the <literal>IS NULL</literal>
922 We have already discussed constants in <xref
923 linkend="sql-syntax-constants">. The following sections discuss
924 the remaining options.
928 <title>Column References</title>
931 A column can be referenced in the form
933 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable>
937 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable>[<replaceable>subscript</replaceable>]
939 (Here, the brackets <literal>[ ]</literal> are meant to appear literally.)
943 <replaceable>correlation</replaceable> is the name of a
944 table (possibly qualified), or an alias for a table defined by means of a
945 <literal>FROM</literal> clause, or
946 the key words <literal>NEW</literal> or <literal>OLD</literal>.
947 (<literal>NEW</literal> and <literal>OLD</literal> can only appear in rewrite rules,
948 while other correlation names can be used in any SQL statement.)
949 The correlation name and separating dot may be omitted if the column name
950 is unique across all the tables being used in the current query. (See also <xref linkend="queries">.)
954 If <replaceable>column</replaceable> is of an array type, then the
955 optional <replaceable>subscript</replaceable> selects a specific
956 element or elements in the array. If no subscript is provided, then the
957 whole array is selected. (See <xref linkend="arrays"> for more about
963 <title>Positional Parameters</title>
966 A positional parameter reference is used to indicate a value
967 that is supplied externally to an SQL statement. Parameters are
968 used in SQL function definitions and in prepared queries. Some
969 client libraries also support specifying data values separately
970 from the SQL command string, in which case parameters are used to
971 refer to the out-of-line data values.
972 The form of a parameter reference is:
974 $<replaceable>number</replaceable>
979 For example, consider the definition of a function,
980 <function>dept</function>, as
983 CREATE FUNCTION dept(text) RETURNS dept
984 AS 'SELECT * FROM dept WHERE name = $1'
988 Here the <literal>$1</literal> will be replaced by the first
989 function argument when the function is invoked.
994 <title>Operator Invocations</title>
997 There are three possible syntaxes for an operator invocation:
999 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
1000 <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
1001 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
1003 where the <replaceable>operator</replaceable> token follows the syntax
1004 rules of <xref linkend="sql-syntax-operators">, or is one of the
1005 keywords <token>AND</token>, <token>OR</token>, and
1006 <token>NOT</token>, or is a qualified operator name
1008 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operatorname</><literal>)</>
1010 Which particular operators exist and whether
1011 they are unary or binary depends on what operators have been
1012 defined by the system or the user. <xref linkend="functions">
1013 describes the built-in operators.
1018 <title>Function Calls</title>
1021 The syntax for a function call is the name of a function
1022 (possibly qualified with a schema name), followed by its argument list
1023 enclosed in parentheses:
1026 <replaceable>function</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> )
1031 For example, the following computes the square root of 2:
1038 The list of built-in functions is in <xref linkend="functions">.
1039 Other functions may be added by the user.
1043 <sect2 id="syntax-aggregates">
1044 <title>Aggregate Expressions</title>
1046 <indexterm zone="syntax-aggregates">
1047 <primary>aggregate functions</primary>
1051 An <firstterm>aggregate expression</firstterm> represents the
1052 application of an aggregate function across the rows selected by a
1053 query. An aggregate function reduces multiple inputs to a single
1054 output value, such as the sum or average of the inputs. The
1055 syntax of an aggregate expression is one of the following:
1058 <replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>)
1059 <replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>)
1060 <replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)
1061 <replaceable>aggregate_name</replaceable> ( * )
1064 where <replaceable>aggregate_name</replaceable> is a previously
1065 defined aggregate (possibly a qualified name), and
1066 <replaceable>expression</replaceable> is
1067 any value expression that does not itself contain an aggregate
1072 The first form of aggregate expression invokes the aggregate
1073 across all input rows for which the given expression yields a
1074 non-null value. (Actually, it is up to the aggregate function
1075 whether to ignore null values or not --- but all the standard ones do.)
1076 The second form is the same as the first, since
1077 <literal>ALL</literal> is the default. The third form invokes the
1078 aggregate for all distinct non-null values of the expression found
1079 in the input rows. The last form invokes the aggregate once for
1080 each input row regardless of null or non-null values; since no
1081 particular input value is specified, it is generally only useful
1082 for the <function>count()</function> aggregate function.
1086 For example, <literal>count(*)</literal> yields the total number
1087 of input rows; <literal>count(f1)</literal> yields the number of
1088 input rows in which <literal>f1</literal> is non-null;
1089 <literal>count(distinct f1)</literal> yields the number of
1090 distinct non-null values of <literal>f1</literal>.
1094 The predefined aggregate functions are described in <xref
1095 linkend="functions-aggregate">. Other aggregate functions may be added
1100 An aggregate expression may only appear in the result list or
1101 <literal>HAVING</> clause of a <command>SELECT</> command.
1102 It is forbidden in other clauses, such as <literal>WHERE</>,
1103 because those clauses are logically evaluated before the results
1104 of aggregates are formed.
1108 When an aggregate expression appears in a subquery (see
1109 <xref linkend="sql-syntax-scalar-subqueries"> and
1110 <xref linkend="functions-subquery">), the aggregate is normally
1111 evaluated over the rows of the subquery. But an exception occurs
1112 if the aggregate's argument contains only outer-level variables:
1113 the aggregate then belongs to the nearest such outer level, and is
1114 evaluated over the rows of that query. The aggregate expression
1115 as a whole is then an outer reference for the subquery it appears in,
1116 and acts as a constant over any one evaluation of that subquery.
1117 The restriction about
1118 appearing only in the result list or <literal>HAVING</> clause
1119 applies with respect to the query level that the aggregate belongs to.
1123 <sect2 id="sql-syntax-type-casts">
1124 <title>Type Casts</title>
1127 <primary>data types</primary>
1128 <secondary>type casts</secondary>
1132 A type cast specifies a conversion from one data type to another.
1133 <productname>PostgreSQL</productname> accepts two equivalent syntaxes
1136 CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> )
1137 <replaceable>expression</replaceable>::<replaceable>type</replaceable>
1139 The <literal>CAST</> syntax conforms to SQL; the syntax with
1140 <literal>::</literal> is historical <productname>PostgreSQL</productname>
1145 When a cast is applied to a value expression of a known type, it
1146 represents a run-time type conversion. The cast will succeed only
1147 if a suitable type conversion function is available. Notice that this
1148 is subtly different from the use of casts with constants, as shown in
1149 <xref linkend="sql-syntax-constants-generic">. A cast applied to an
1150 unadorned string literal represents the initial assignment of a type
1151 to a literal constant value, and so it will succeed for any type
1152 (if the contents of the string literal are acceptable input syntax for the
1157 An explicit type cast may usually be omitted if there is no ambiguity as
1158 to the type that a value expression must produce (for example, when it is
1159 assigned to a table column); the system will automatically apply a
1160 type cast in such cases. However, automatic casting is only done for
1161 casts that are marked <quote>OK to apply implicitly</>
1162 in the system catalogs. Other casts must be invoked with
1163 explicit casting syntax. This restriction is intended to prevent
1164 surprising conversions from being applied silently.
1168 It is also possible to specify a type cast using a function-like
1171 <replaceable>typename</replaceable> ( <replaceable>expression</replaceable> )
1173 However, this only works for types whose names are also valid as
1174 function names. For example, <literal>double precision</literal>
1175 can't be used this way, but the equivalent <literal>float8</literal>
1176 can. Also, the names <literal>interval</>, <literal>time</>, and
1177 <literal>timestamp</> can only be used in this fashion if they are
1178 double-quoted, because of syntactic conflicts. Therefore, the use of
1179 the function-like cast syntax leads to inconsistencies and should
1180 probably be avoided in new applications.
1182 (The function-like syntax is in fact just a function call. When
1183 one of the two standard cast syntaxes is used to do a run-time
1184 conversion, it will internally invoke a registered function to
1185 perform the conversion. By convention, these conversion functions
1186 have the same name as their output type, and thus the <quote>function-like
1187 syntax</> is nothing more than a direct invocation of the underlying
1188 conversion function. Obviously, this is not something that a portable
1189 application should rely on.)
1193 <sect2 id="sql-syntax-scalar-subqueries">
1194 <title>Scalar Subqueries</title>
1197 A scalar subquery is an ordinary
1198 <command>SELECT</command> query in parentheses that returns exactly one
1199 row with one column. (See <xref linkend="queries"> for information about writing queries.)
1200 The <command>SELECT</command> query is executed
1201 and the single returned value is used in the surrounding value expression.
1202 It is an error to use a query that
1203 returns more than one row or more than one column as a scalar subquery.
1204 (But if, during a particular execution, the subquery returns no rows,
1205 there is no error; the scalar result is taken to be null.)
1206 The subquery can refer to variables from the surrounding query,
1207 which will act as constants during any one evaluation of the subquery.
1208 See also <xref linkend="functions-subquery">.
1212 For example, the following finds the largest city population in each
1215 SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
1221 <sect2 id="sql-syntax-array-constructors">
1222 <title>Array Constructors</title>
1225 <primary>arrays</primary>
1226 <secondary>constructors</secondary>
1230 An <firstterm>array constructor</> is an expression that builds an
1231 array value from values for its member elements. A simple array
1233 consists of the keyword <literal>ARRAY</literal>, a left square bracket
1234 <literal>[</>, one or more expressions (separated by commas) for the
1235 array element values, and finally a right square bracket <literal>]</>.
1238 SELECT ARRAY[1,2,3+4];
1244 The array element type is the common type of the member expressions,
1245 determined using the same rules as for <literal>UNION</> or
1246 <literal>CASE</> constructs (see <xref linkend="typeconv-union-case">).
1250 Multidimensional array values can be built by nesting array
1252 In the inner constructors, the keyword <literal>ARRAY</literal> may
1253 be omitted. For example, these produce the same result:
1256 SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
1262 SELECT ARRAY[[1,2],[3,4]];
1269 Since multidimensional arrays must be rectangular, inner constructors
1270 at the same level must produce sub-arrays of identical dimensions.
1274 It is also possible to construct an array from the results of a
1275 subquery. In this form, the array constructor is written with the
1276 keyword <literal>ARRAY</literal> followed by a parenthesized (not
1277 bracketed) subquery. For example:
1279 SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
1281 -------------------------------------------------------------
1282 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
1285 The sub-select must return a single column. The
1286 resulting one-dimensional array will have an element for each row in the
1287 sub-select result, with an element type matching that of the sub-select's
1292 The subscripts of an array value built with <literal>ARRAY</literal>
1293 always begin with one. For more information about arrays, see
1294 <xref linkend="arrays">.
1299 <sect2 id="syntax-express-eval">
1300 <title>Expression Evaluation Rules</title>
1303 The order of evaluation of subexpressions is not defined. In
1304 particular, the inputs of an operator or function are not necessarily
1305 evaluated left-to-right or in any other fixed order.
1309 Furthermore, if the result of an expression can be determined by
1310 evaluating only some parts of it, then other subexpressions
1311 might not be evaluated at all. For instance, if one wrote
1313 SELECT true OR somefunc();
1315 then <literal>somefunc()</literal> would (probably) not be called
1316 at all. The same would be the case if one wrote
1318 SELECT somefunc() OR true;
1320 Note that this is not the same as the left-to-right
1321 <quote>short-circuiting</quote> of Boolean operators that is found
1322 in some programming languages.
1326 As a consequence, it is unwise to use functions with side effects
1327 as part of complex expressions. It is particularly dangerous to
1328 rely on side effects or evaluation order in <literal>WHERE</> and <literal>HAVING</> clauses,
1329 since those clauses are extensively reprocessed as part of
1330 developing an execution plan. Boolean
1331 expressions (<literal>AND</>/<literal>OR</>/<literal>NOT</> combinations) in those clauses may be reorganized
1332 in any manner allowed by the laws of Boolean algebra.
1336 When it is essential to force evaluation order, a <literal>CASE</>
1337 construct (see <xref linkend="functions-conditional">) may be
1338 used. For example, this is an untrustworthy way of trying to
1339 avoid division by zero in a <literal>WHERE</> clause:
1341 SELECT ... WHERE x <> 0 AND y/x > 1.5;
1345 SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
1347 A <literal>CASE</> construct used in this fashion will defeat optimization
1348 attempts, so it should only be done when necessary. (In this particular
1349 example, it would doubtless be best to sidestep the problem by writing
1350 <literal>y > 1.5*x</> instead.)
1357 <!-- Keep this comment at the end of the file
1362 sgml-minimize-attributes:nil
1363 sgml-always-quote-attributes:t
1366 sgml-parent-document:nil
1367 sgml-default-dtd-file:"./reference.ced"
1368 sgml-exposed-tags:nil
1369 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1370 sgml-local-ecat-files:nil