2 $Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.81 2003/08/09 22:50:22 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.
457 Individual array elements can be placed between double-quote
458 marks (<literal>"</literal>) <!-- " --> to avoid ambiguity
459 problems with respect to whitespace. Without quote marks, the
460 array-value parser will skip leading whitespace.
464 (Array constants are actually only a special case of the generic
465 type constants discussed in the previous section. The constant
466 is initially treated as a string and passed to the array input
467 conversion routine. An explicit type specification might be
474 <sect2 id="sql-syntax-operators">
475 <title>Operators</title>
477 <indexterm zone="sql-syntax-operators">
478 <primary>operators</primary>
479 <secondary>syntax</secondary>
483 An operator is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
484 (63 by default) characters from the following list:
486 + - * / < > = ~ ! @ # % ^ & | ` ?
489 There are a few restrictions on operator names, however:
493 <literal>--</literal> and <literal>/*</literal> cannot appear
494 anywhere in an operator name, since they will be taken as the
501 A multiple-character operator name cannot end in <literal>+</> or <literal>-</>,
502 unless the name also contains at least one of these characters:
504 ~ ! @ # % ^ & | ` ?
506 For example, <literal>@-</literal> is an allowed operator name,
507 but <literal>*-</literal> is not. This restriction allows
508 <productname>PostgreSQL</productname> to parse SQL-compliant
509 queries without requiring spaces between tokens.
516 When working with non-SQL-standard operator names, you will usually
517 need to separate adjacent operators with spaces to avoid ambiguity.
518 For example, if you have defined a left unary operator named <literal>@</literal>,
519 you cannot write <literal>X*@Y</literal>; you must write
520 <literal>X* @Y</literal> to ensure that
521 <productname>PostgreSQL</productname> reads it as two operator names
527 <title>Special Characters</title>
530 Some characters that are not alphanumeric have a special meaning
531 that is different from being an operator. Details on the usage can
532 be found at the location where the respective syntax element is
533 described. This section only exists to advise the existence and
534 summarize the purposes of these characters.
539 A dollar sign (<literal>$</literal>) followed by digits is used
540 to represent a positional parameter in the body of a function
541 definition or a prepared statement. In other contexts the
542 dollar sign may be part of an identifier.
548 Parentheses (<literal>()</literal>) have their usual meaning to
549 group expressions and enforce precedence. In some cases
550 parentheses are required as part of the fixed syntax of a
551 particular SQL command.
557 Brackets (<literal>[]</literal>) are used to select the elements
558 of an array. See <xref linkend="arrays"> for more information
565 Commas (<literal>,</literal>) are used in some syntactical
566 constructs to separate the elements of a list.
572 The semicolon (<literal>;</literal>) terminates an SQL command.
573 It cannot appear anywhere within a command, except within a
574 string constant or quoted identifier.
580 The colon (<literal>:</literal>) is used to select
581 <quote>slices</quote> from arrays. (See <xref
582 linkend="arrays">.) In certain SQL dialects (such as Embedded
583 SQL), the colon is used to prefix variable names.
589 The asterisk (<literal>*</literal>) has a special meaning when
590 used in the <command>SELECT</command> command or with the
591 <function>COUNT</function> aggregate function.
597 The period (<literal>.</literal>) is used in numeric
598 constants, and to separate schema, table, and column names.
606 <sect2 id="sql-syntax-comments">
607 <title>Comments</title>
609 <indexterm zone="sql-syntax-comments">
610 <primary>comments</primary>
611 <secondary>in SQL</secondary>
615 A comment is an arbitrary sequence of characters beginning with
616 double dashes and extending to the end of the line, e.g.:
618 -- This is a standard SQL92 comment
623 Alternatively, C-style block comments can be used:
626 * with nesting: /* nested block comment */
629 where the comment begins with <literal>/*</literal> and extends to
630 the matching occurrence of <literal>*/</literal>. These block
631 comments nest, as specified in SQL99 but unlike C, so that one can
632 comment out larger blocks of code that may contain existing block
637 A comment is removed from the input stream before further syntax
638 analysis and is effectively replaced by whitespace.
642 <sect2 id="sql-precedence">
643 <title>Lexical Precedence</title>
645 <indexterm zone="sql-precedence">
646 <primary>operators</primary>
647 <secondary>precedence</secondary>
651 <xref linkend="sql-precedence-table"> shows the precedence and
652 associativity of the operators in <productname>PostgreSQL</>.
653 Most operators have the same precedence and are left-associative.
654 The precedence and associativity of the operators is hard-wired
655 into the parser. This may lead to non-intuitive behavior; for
656 example the Boolean operators <literal><</> and
657 <literal>></> have a different precedence than the Boolean
658 operators <literal><=</> and <literal>>=</>. Also, you will
659 sometimes need to add parentheses when using combinations of
660 binary and unary operators. For instance
668 because the parser has no idea -- until it is too late -- that
669 <token>!</token> is defined as a postfix operator, not an infix one.
670 To get the desired behavior in this case, you must write
674 This is the price one pays for extensibility.
677 <table id="sql-precedence-table">
678 <title>Operator Precedence (decreasing)</title>
683 <entry>Operator/Element</entry>
684 <entry>Associativity</entry>
685 <entry>Description</entry>
691 <entry><token>.</token></entry>
693 <entry>table/column name separator</entry>
697 <entry><token>::</token></entry>
699 <entry><productname>PostgreSQL</productname>-style typecast</entry>
703 <entry><token>[</token> <token>]</token></entry>
705 <entry>array element selection</entry>
709 <entry><token>-</token></entry>
711 <entry>unary minus</entry>
715 <entry><token>^</token></entry>
717 <entry>exponentiation</entry>
721 <entry><token>*</token> <token>/</token> <token>%</token></entry>
723 <entry>multiplication, division, modulo</entry>
727 <entry><token>+</token> <token>-</token></entry>
729 <entry>addition, subtraction</entry>
733 <entry><token>IS</token></entry>
735 <entry><literal>IS TRUE</>, <literal>IS FALSE</>, <literal>IS UNKNOWN</>, <literal>IS NULL</></entry>
739 <entry><token>ISNULL</token></entry>
741 <entry>test for null</entry>
745 <entry><token>NOTNULL</token></entry>
747 <entry>test for not null</entry>
751 <entry>(any other)</entry>
753 <entry>all other native and user-defined operators</entry>
757 <entry><token>IN</token></entry>
759 <entry>set membership</entry>
763 <entry><token>BETWEEN</token></entry>
765 <entry>containment</entry>
769 <entry><token>OVERLAPS</token></entry>
771 <entry>time interval overlap</entry>
775 <entry><token>LIKE</token> <token>ILIKE</token> <token>SIMILAR</token></entry>
777 <entry>string pattern matching</entry>
781 <entry><token><</token> <token>></token></entry>
783 <entry>less than, greater than</entry>
787 <entry><token>=</token></entry>
789 <entry>equality, assignment</entry>
793 <entry><token>NOT</token></entry>
795 <entry>logical negation</entry>
799 <entry><token>AND</token></entry>
801 <entry>logical conjunction</entry>
805 <entry><token>OR</token></entry>
807 <entry>logical disjunction</entry>
814 Note that the operator precedence rules also apply to user-defined
815 operators that have the same names as the built-in operators
816 mentioned above. For example, if you define a
817 <quote>+</quote> operator for some custom data type it will have
818 the same precedence as the built-in <quote>+</quote> operator, no
819 matter what yours does.
823 When a schema-qualified operator name is used in the
824 <literal>OPERATOR</> syntax, as for example in
826 SELECT 3 OPERATOR(pg_catalog.+) 4;
828 the <literal>OPERATOR</> construct is taken to have the default precedence
829 shown in <xref linkend="sql-precedence-table"> for <quote>any other</> operator. This is true no matter
830 which specific operator name appears inside <literal>OPERATOR()</>.
835 <sect1 id="sql-expressions">
836 <title>Value Expressions</title>
839 Value expressions are used in a variety of contexts, such
840 as in the target list of the <command>SELECT</command> command, as
841 new column values in <command>INSERT</command> or
842 <command>UPDATE</command>, or in search conditions in a number of
843 commands. The result of a value expression is sometimes called a
844 <firstterm>scalar</firstterm>, to distinguish it from the result of
845 a table expression (which is a table). Value expressions are
846 therefore also called <firstterm>scalar expressions</firstterm> (or
847 even simply <firstterm>expressions</firstterm>). The expression
848 syntax allows the calculation of values from primitive parts using
849 arithmetic, logical, set, and other operations.
853 A value expression is one of the following:
858 A constant or literal value; see <xref linkend="sql-syntax-constants">.
870 A positional parameter reference, in the body of a function definition
871 or prepared statement.
877 An operator invocation.
889 An aggregate expression.
907 An array constructor.
913 Another value expression in parentheses, useful to group subexpressions and override precedence.
920 In addition to this list, there are a number of constructs that can
921 be classified as an expression but do not follow any general syntax
922 rules. These generally have the semantics of a function or
923 operator and are explained in the appropriate location in <xref
924 linkend="functions">. An example is the <literal>IS NULL</literal>
929 We have already discussed constants in <xref
930 linkend="sql-syntax-constants">. The following sections discuss
931 the remaining options.
935 <title>Column References</title>
938 A column can be referenced in the form
940 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable>
944 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable>[<replaceable>subscript</replaceable>]
946 (Here, the brackets <literal>[ ]</literal> are meant to appear literally.)
950 <replaceable>correlation</replaceable> is the name of a
951 table (possibly qualified), or an alias for a table defined by means of a
952 <literal>FROM</literal> clause, or
953 the key words <literal>NEW</literal> or <literal>OLD</literal>.
954 (<literal>NEW</literal> and <literal>OLD</literal> can only appear in rewrite rules,
955 while other correlation names can be used in any SQL statement.)
956 The correlation name and separating dot may be omitted if the column name
957 is unique across all the tables being used in the current query. (See also <xref linkend="queries">.)
961 If <replaceable>column</replaceable> is of an array type, then the
962 optional <replaceable>subscript</replaceable> selects a specific
963 element or elements in the array. If no subscript is provided, then the
964 whole array is selected. (See <xref linkend="arrays"> for more about
970 <title>Positional Parameters</title>
973 A positional parameter reference is used to indicate a value
974 that is supplied externally to an SQL statement. Parameters are
975 used in SQL function definitions and in prepared queries. Some
976 client libraries also support specifying data values separately
977 from the SQL command string, in which case parameters are used to
978 refer to the out-of-line data values.
979 The form of a parameter reference is:
981 $<replaceable>number</replaceable>
986 For example, consider the definition of a function,
987 <function>dept</function>, as
990 CREATE FUNCTION dept(text) RETURNS dept
991 AS 'SELECT * FROM dept WHERE name = $1'
995 Here the <literal>$1</literal> will be replaced by the first
996 function argument when the function is invoked.
1001 <title>Operator Invocations</title>
1004 There are three possible syntaxes for an operator invocation:
1006 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
1007 <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
1008 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
1010 where the <replaceable>operator</replaceable> token follows the syntax
1011 rules of <xref linkend="sql-syntax-operators">, or is one of the
1012 keywords <token>AND</token>, <token>OR</token>, and
1013 <token>NOT</token>, or is a qualified operator name
1015 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operatorname</><literal>)</>
1017 Which particular operators exist and whether
1018 they are unary or binary depends on what operators have been
1019 defined by the system or the user. <xref linkend="functions">
1020 describes the built-in operators.
1025 <title>Function Calls</title>
1028 The syntax for a function call is the name of a function
1029 (possibly qualified with a schema name), followed by its argument list
1030 enclosed in parentheses:
1033 <replaceable>function</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> )
1038 For example, the following computes the square root of 2:
1045 The list of built-in functions is in <xref linkend="functions">.
1046 Other functions may be added by the user.
1050 <sect2 id="syntax-aggregates">
1051 <title>Aggregate Expressions</title>
1053 <indexterm zone="syntax-aggregates">
1054 <primary>aggregate functions</primary>
1058 An <firstterm>aggregate expression</firstterm> represents the
1059 application of an aggregate function across the rows selected by a
1060 query. An aggregate function reduces multiple inputs to a single
1061 output value, such as the sum or average of the inputs. The
1062 syntax of an aggregate expression is one of the following:
1065 <replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>)
1066 <replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>)
1067 <replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)
1068 <replaceable>aggregate_name</replaceable> ( * )
1071 where <replaceable>aggregate_name</replaceable> is a previously
1072 defined aggregate (possibly a qualified name), and
1073 <replaceable>expression</replaceable> is
1074 any value expression that does not itself contain an aggregate
1079 The first form of aggregate expression invokes the aggregate
1080 across all input rows for which the given expression yields a
1081 non-null value. (Actually, it is up to the aggregate function
1082 whether to ignore null values or not --- but all the standard ones do.)
1083 The second form is the same as the first, since
1084 <literal>ALL</literal> is the default. The third form invokes the
1085 aggregate for all distinct non-null values of the expression found
1086 in the input rows. The last form invokes the aggregate once for
1087 each input row regardless of null or non-null values; since no
1088 particular input value is specified, it is generally only useful
1089 for the <function>count()</function> aggregate function.
1093 For example, <literal>count(*)</literal> yields the total number
1094 of input rows; <literal>count(f1)</literal> yields the number of
1095 input rows in which <literal>f1</literal> is non-null;
1096 <literal>count(distinct f1)</literal> yields the number of
1097 distinct non-null values of <literal>f1</literal>.
1101 The predefined aggregate functions are described in <xref
1102 linkend="functions-aggregate">. Other aggregate functions may be added
1107 An aggregate expression may only appear in the result list or
1108 <literal>HAVING</> clause of a <command>SELECT</> command.
1109 It is forbidden in other clauses, such as <literal>WHERE</>,
1110 because those clauses are logically evaluated before the results
1111 of aggregates are formed.
1115 When an aggregate expression appears in a subquery (see
1116 <xref linkend="sql-syntax-scalar-subqueries"> and
1117 <xref linkend="functions-subquery">), the aggregate is normally
1118 evaluated over the rows of the subquery. But an exception occurs
1119 if the aggregate's argument contains only outer-level variables:
1120 the aggregate then belongs to the nearest such outer level, and is
1121 evaluated over the rows of that query. The aggregate expression
1122 as a whole is then an outer reference for the subquery it appears in,
1123 and acts as a constant over any one evaluation of that subquery.
1124 The restriction about
1125 appearing only in the result list or <literal>HAVING</> clause
1126 applies with respect to the query level that the aggregate belongs to.
1130 <sect2 id="sql-syntax-type-casts">
1131 <title>Type Casts</title>
1134 <primary>data types</primary>
1135 <secondary>type casts</secondary>
1139 A type cast specifies a conversion from one data type to another.
1140 <productname>PostgreSQL</productname> accepts two equivalent syntaxes
1143 CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> )
1144 <replaceable>expression</replaceable>::<replaceable>type</replaceable>
1146 The <literal>CAST</> syntax conforms to SQL; the syntax with
1147 <literal>::</literal> is historical <productname>PostgreSQL</productname>
1152 When a cast is applied to a value expression of a known type, it
1153 represents a run-time type conversion. The cast will succeed only
1154 if a suitable type conversion function is available. Notice that this
1155 is subtly different from the use of casts with constants, as shown in
1156 <xref linkend="sql-syntax-constants-generic">. A cast applied to an
1157 unadorned string literal represents the initial assignment of a type
1158 to a literal constant value, and so it will succeed for any type
1159 (if the contents of the string literal are acceptable input syntax for the
1164 An explicit type cast may usually be omitted if there is no ambiguity as
1165 to the type that a value expression must produce (for example, when it is
1166 assigned to a table column); the system will automatically apply a
1167 type cast in such cases. However, automatic casting is only done for
1168 casts that are marked <quote>OK to apply implicitly</>
1169 in the system catalogs. Other casts must be invoked with
1170 explicit casting syntax. This restriction is intended to prevent
1171 surprising conversions from being applied silently.
1175 It is also possible to specify a type cast using a function-like
1178 <replaceable>typename</replaceable> ( <replaceable>expression</replaceable> )
1180 However, this only works for types whose names are also valid as
1181 function names. For example, <literal>double precision</literal>
1182 can't be used this way, but the equivalent <literal>float8</literal>
1183 can. Also, the names <literal>interval</>, <literal>time</>, and
1184 <literal>timestamp</> can only be used in this fashion if they are
1185 double-quoted, because of syntactic conflicts. Therefore, the use of
1186 the function-like cast syntax leads to inconsistencies and should
1187 probably be avoided in new applications.
1189 (The function-like syntax is in fact just a function call. When
1190 one of the two standard cast syntaxes is used to do a run-time
1191 conversion, it will internally invoke a registered function to
1192 perform the conversion. By convention, these conversion functions
1193 have the same name as their output type, but this is not something
1194 that a portable application should rely on.)
1198 <sect2 id="sql-syntax-scalar-subqueries">
1199 <title>Scalar Subqueries</title>
1202 A scalar subquery is an ordinary
1203 <command>SELECT</command> query in parentheses that returns exactly one
1204 row with one column. (See <xref linkend="queries"> for information about writing queries.)
1205 The <command>SELECT</command> query is executed
1206 and the single returned value is used in the surrounding value expression.
1207 It is an error to use a query that
1208 returns more than one row or more than one column as a scalar subquery.
1209 (But if, during a particular execution, the subquery returns no rows,
1210 there is no error; the scalar result is taken to be null.)
1211 The subquery can refer to variables from the surrounding query,
1212 which will act as constants during any one evaluation of the subquery.
1213 See also <xref linkend="functions-subquery">.
1217 For example, the following finds the largest city population in each
1220 SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
1226 <sect2 id="sql-syntax-array-constructors">
1227 <title>Array Constructors</title>
1230 <primary>arrays</primary>
1231 <secondary>constructors</secondary>
1235 An <firstterm>array constructor</> is an expression that builds an
1236 array value from values for its member elements. A simple array
1238 consists of the keyword <literal>ARRAY</literal>, a left square bracket
1239 <literal>[</>, one or more expressions (separated by commas) for the
1240 array element values, and finally a right square bracket <literal>]</>.
1243 SELECT ARRAY[1,2,3+4];
1249 The array element type is the common type of the member expressions,
1250 determined using the same rules as for <literal>UNION</> or
1251 <literal>CASE</> constructs (see <xref linkend="typeconv-union-case">).
1255 Multidimensional array values can be built by nesting array
1257 In the inner constructors, the keyword <literal>ARRAY</literal> may
1258 be omitted. For example, these produce the same result:
1261 SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
1267 SELECT ARRAY[[1,2],[3,4]];
1274 Since multidimensional arrays must be rectangular, inner constructors
1275 at the same level must produce sub-arrays of identical dimensions.
1279 It is also possible to construct an array from the results of a
1280 subquery. In this form, the array constructor is written with the
1281 keyword <literal>ARRAY</literal> followed by a parenthesized (not
1282 bracketed) subquery. For example:
1284 SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
1286 -------------------------------------------------------------
1287 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
1290 The sub-select must return a single column. The
1291 resulting one-dimensional array will have an element for each row in the
1292 sub-select result, with an element type matching that of the sub-select's
1297 The subscripts of an array value built with <literal>ARRAY</literal>
1298 always begin with one. For more information about arrays, see
1299 <xref linkend="arrays">.
1304 <sect2 id="syntax-express-eval">
1305 <title>Expression Evaluation Rules</title>
1308 The order of evaluation of subexpressions is not defined. In
1309 particular, the inputs of an operator or function are not necessarily
1310 evaluated left-to-right or in any other fixed order.
1314 Furthermore, if the result of an expression can be determined by
1315 evaluating only some parts of it, then other subexpressions
1316 might not be evaluated at all. For instance, if one wrote
1318 SELECT true OR somefunc();
1320 then <literal>somefunc()</literal> would (probably) not be called
1321 at all. The same would be the case if one wrote
1323 SELECT somefunc() OR true;
1325 Note that this is not the same as the left-to-right
1326 <quote>short-circuiting</quote> of Boolean operators that is found
1327 in some programming languages.
1331 As a consequence, it is unwise to use functions with side effects
1332 as part of complex expressions. It is particularly dangerous to
1333 rely on side effects or evaluation order in <literal>WHERE</> and <literal>HAVING</> clauses,
1334 since those clauses are extensively reprocessed as part of
1335 developing an execution plan. Boolean
1336 expressions (<literal>AND</>/<literal>OR</>/<literal>NOT</> combinations) in those clauses may be reorganized
1337 in any manner allowed by the laws of Boolean algebra.
1341 When it is essential to force evaluation order, a <literal>CASE</>
1342 construct (see <xref linkend="functions-conditional">) may be
1343 used. For example, this is an untrustworthy way of trying to
1344 avoid division by zero in a <literal>WHERE</> clause:
1346 SELECT ... WHERE x <> 0 AND y/x > 1.5;
1350 SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
1352 A <literal>CASE</> construct used in this fashion will defeat optimization attempts,
1353 so it should only be done when necessary.
1360 <!-- Keep this comment at the end of the file
1365 sgml-minimize-attributes:nil
1366 sgml-always-quote-attributes:t
1369 sgml-parent-document:nil
1370 sgml-default-dtd-file:"./reference.ced"
1371 sgml-exposed-tags:nil
1372 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1373 sgml-local-ecat-files:nil