2 $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.105 2005/11/04 23:14:02 petere 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 <primary>token</primary>
34 SQL input consists of a sequence of
35 <firstterm>commands</firstterm>. A command is composed of a
36 sequence of <firstterm>tokens</firstterm>, terminated by a
37 semicolon (<quote>;</quote>). The end of the input stream also
38 terminates a command. Which tokens are valid depends on the syntax
39 of the particular command.
43 A token can be a <firstterm>key word</firstterm>, an
44 <firstterm>identifier</firstterm>, a <firstterm>quoted
45 identifier</firstterm>, a <firstterm>literal</firstterm> (or
46 constant), or a special character symbol. Tokens are normally
47 separated by whitespace (space, tab, newline), but need not be if
48 there is no ambiguity (which is generally only the case if a
49 special character is adjacent to some other token type).
53 Additionally, <firstterm>comments</firstterm> can occur in SQL
54 input. They are not tokens, they are effectively equivalent to
59 For example, the following is (syntactically) valid SQL input:
61 SELECT * FROM MY_TABLE;
62 UPDATE MY_TABLE SET A = 5;
63 INSERT INTO MY_TABLE VALUES (3, 'hi there');
65 This is a sequence of three commands, one per line (although this
66 is not required; more than one command can be on a line, and
67 commands can usefully be split across lines).
71 The SQL syntax is not very consistent regarding what tokens
72 identify commands and which are operands or parameters. The first
73 few tokens are generally the command name, so in the above example
74 we would usually speak of a <quote>SELECT</quote>, an
75 <quote>UPDATE</quote>, and an <quote>INSERT</quote> command. But
76 for instance the <command>UPDATE</command> command always requires
77 a <token>SET</token> token to appear in a certain position, and
78 this particular variation of <command>INSERT</command> also
79 requires a <token>VALUES</token> in order to be complete. The
80 precise syntax rules for each command are described in <xref linkend="reference">.
83 <sect2 id="sql-syntax-identifiers">
84 <title>Identifiers and Key Words</title>
86 <indexterm zone="sql-syntax-identifiers">
87 <primary>identifier</primary>
88 <secondary>syntax of</secondary>
91 <indexterm zone="sql-syntax-identifiers">
92 <primary>name</primary>
93 <secondary>syntax of</secondary>
96 <indexterm zone="sql-syntax-identifiers">
97 <primary>key word</primary>
98 <secondary>syntax of</secondary>
102 Tokens such as <token>SELECT</token>, <token>UPDATE</token>, or
103 <token>VALUES</token> in the example above are examples of
104 <firstterm>key words</firstterm>, that is, words that have a fixed
105 meaning in the SQL language. The tokens <token>MY_TABLE</token>
106 and <token>A</token> are examples of
107 <firstterm>identifiers</firstterm>. They identify names of
108 tables, columns, or other database objects, depending on the
109 command they are used in. Therefore they are sometimes simply
110 called <quote>names</quote>. Key words and identifiers have the
111 same lexical structure, meaning that one cannot know whether a
112 token is an identifier or a key word without knowing the language.
113 A complete list of key words can be found in <xref
114 linkend="sql-keywords-appendix">.
118 SQL identifiers and key words must begin with a letter
119 (<literal>a</literal>-<literal>z</literal>, but also letters with
120 diacritical marks and non-Latin letters) or an underscore
121 (<literal>_</literal>). Subsequent characters in an identifier or
122 key word can be letters, underscores, digits
123 (<literal>0</literal>-<literal>9</literal>), or dollar signs
124 (<literal>$</>). Note that dollar signs are not allowed in identifiers
125 according to the letter of the SQL standard, so their use may render
126 applications less portable.
127 The SQL standard will not define a key word that contains
128 digits or starts or ends with an underscore, so identifiers of this
129 form are safe against possible conflict with future extensions of the
134 <indexterm><primary>identifier</primary><secondary>length</secondary></indexterm>
135 The system uses no more than <symbol>NAMEDATALEN</symbol>-1
136 characters of an identifier; longer names can be written in
137 commands, but they will be truncated. By default,
138 <symbol>NAMEDATALEN</symbol> is 64 so the maximum identifier
139 length is 63. If this limit is problematic, it can be raised by
140 changing the <symbol>NAMEDATALEN</symbol> constant in
141 <filename>src/include/postgres_ext.h</filename>.
146 <primary>case sensitivity</primary>
147 <secondary>of SQL commands</secondary>
149 Identifier and key word names are case insensitive. Therefore
151 UPDATE MY_TABLE SET A = 5;
153 can equivalently be written as
155 uPDaTE my_TabLE SeT a = 5;
157 A convention often used is to write key words in upper
158 case and names in lower case, e.g.,
160 UPDATE my_table SET a = 5;
166 <primary>quotation marks</primary>
167 <secondary>and identifiers</secondary>
169 There is a second kind of identifier: the <firstterm>delimited
170 identifier</firstterm> or <firstterm>quoted
171 identifier</firstterm>. It is formed by enclosing an arbitrary
172 sequence of characters in double-quotes
173 (<literal>"</literal>). <!-- " font-lock mania --> A delimited
174 identifier is always an identifier, never a key word. So
175 <literal>"select"</literal> could be used to refer to a column or
176 table named <quote>select</quote>, whereas an unquoted
177 <literal>select</literal> would be taken as a key word and
178 would therefore provoke a parse error when used where a table or
179 column name is expected. The example can be written with quoted
180 identifiers like this:
182 UPDATE "my_table" SET "a" = 5;
187 Quoted identifiers can contain any character other than a double
188 quote itself. (To include a double quote, write two double quotes.)
189 This allows constructing table or column names that would
190 otherwise not be possible, such as ones containing spaces or
191 ampersands. The length limitation still applies.
195 Quoting an identifier also makes it case-sensitive, whereas
196 unquoted names are always folded to lower case. For example, the
197 identifiers <literal>FOO</literal>, <literal>foo</literal>, and
198 <literal>"foo"</literal> are considered the same by
199 <productname>PostgreSQL</productname>, but
200 <literal>"Foo"</literal> and <literal>"FOO"</literal> are
201 different from these three and each other. (The folding of
202 unquoted names to lower case in <productname>PostgreSQL</> is
203 incompatible with the SQL standard, which says that unquoted names
204 should be folded to upper case. Thus, <literal>foo</literal>
205 should be equivalent to <literal>"FOO"</literal> not
206 <literal>"foo"</literal> according to the standard. If you want
207 to write portable applications you are advised to always quote a
208 particular name or never quote it.)
213 <sect2 id="sql-syntax-constants">
214 <title>Constants</title>
216 <indexterm zone="sql-syntax-constants">
217 <primary>constant</primary>
221 There are three kinds of <firstterm>implicitly-typed
222 constants</firstterm> in <productname>PostgreSQL</productname>:
223 strings, bit strings, and numbers.
224 Constants can also be specified with explicit types, which can
225 enable more accurate representation and more efficient handling by
226 the system. These alternatives are discussed in the following
230 <sect3 id="sql-syntax-strings">
231 <title>String Constants</title>
233 <indexterm zone="sql-syntax-strings">
234 <primary>character string</primary>
235 <secondary>constant</secondary>
240 <primary>quotation marks</primary>
241 <secondary>escaping</secondary>
243 A string constant in SQL is an arbitrary sequence of characters
244 bounded by single quotes (<literal>'</literal>), for example
245 <literal>'This is a string'</literal>. The standard-compliant way of
246 writing a single-quote character within a string constant is to
247 write two adjacent single quotes, e.g.
248 <literal>'Dianne''s horse'</literal>.
249 <productname>PostgreSQL</productname> also allows single quotes
250 to be escaped with a backslash (<literal>\'</literal>). However,
251 future versions of <productname>PostgreSQL</productname> will not
252 allow this, so applications using backslashes should convert to the
253 standard-compliant method outlined above.
257 Another <productname>PostgreSQL</productname> extension is that
258 C-style backslash escapes are available: <literal>\b</literal> is a
259 backspace, <literal>\f</literal> is a form feed,
260 <literal>\n</literal> is a newline, <literal>\r</literal> is a
261 carriage return, <literal>\t</literal> is a tab. Also supported is
262 <literal>\<replaceable>digits</replaceable></literal>, where
263 <replaceable>digits</replaceable> represents an octal byte value, and
264 <literal>\x<replaceable>hexdigits</replaceable></literal>, where
265 <replaceable>hexdigits</replaceable> represents a hexadecimal byte value.
266 (It is your responsibility that the byte sequences you create are
267 valid characters in the server character set encoding.) Any other
268 character following a backslash is taken literally. Thus, to
269 include a backslash in a string constant, write two backslashes.
274 While ordinary strings now support C-style backslash escapes,
275 future versions will generate warnings for such usage and
276 eventually treat backslashes as literal characters to be
277 standard-conforming. The proper way to specify escape processing is
278 to use the escape string syntax to indicate that escape
279 processing is desired. Escape string syntax is specified by writing
280 the letter <literal>E</literal> (upper or lower case) just before
281 the string, e.g. <literal>E'\041'</>. This method will work in all
282 future versions of <productname>PostgreSQL</productname>.
287 The character with the code zero cannot be in a string constant.
291 Two string constants that are only separated by whitespace
292 <emphasis>with at least one newline</emphasis> are concatenated
293 and effectively treated as if the string had been written in one
294 constant. For example:
307 is not valid syntax. (This slightly bizarre behavior is specified
308 by <acronym>SQL</acronym>; <productname>PostgreSQL</productname> is
309 following the standard.)
313 <sect3 id="sql-syntax-dollar-quoting">
314 <title>Dollar-Quoted String Constants</title>
317 <primary>dollar quoting</primary>
321 While the standard syntax for specifying string constants is usually
322 convenient, it can be difficult to understand when the desired string
323 contains many single quotes or backslashes, since each of those must
324 be doubled. To allow more readable queries in such situations,
325 <productname>PostgreSQL</productname> provides another way, called
326 <quote>dollar quoting</quote>, to write string constants.
327 A dollar-quoted string constant
328 consists of a dollar sign (<literal>$</literal>), an optional
329 <quote>tag</quote> of zero or more characters, another dollar
330 sign, an arbitrary sequence of characters that makes up the
331 string content, a dollar sign, the same tag that began this
332 dollar quote, and a dollar sign. For example, here are two
333 different ways to specify the string <quote>Dianne's horse</>
334 using dollar quoting:
337 $SomeTag$Dianne's horse$SomeTag$
339 Notice that inside the dollar-quoted string, single quotes can be
340 used without needing to be escaped. Indeed, no characters inside
341 a dollar-quoted string are ever escaped: the string content is always
342 written literally. Backslashes are not special, and neither are
343 dollar signs, unless they are part of a sequence matching the opening
348 It is possible to nest dollar-quoted string constants by choosing
349 different tags at each nesting level. This is most commonly used in
350 writing function definitions. For example:
354 RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
358 Here, the sequence <literal>$q$[\t\r\n\v\\]$q$</> represents a
359 dollar-quoted literal string <literal>[\t\r\n\v\\]</>, which will
360 be recognized when the function body is executed by
361 <productname>PostgreSQL</>. But since the sequence does not match
362 the outer dollar quoting delimiter <literal>$function$</>, it is
363 just some more characters within the constant so far as the outer
368 The tag, if any, of a dollar-quoted string follows the same rules
369 as an unquoted identifier, except that it cannot contain a dollar sign.
370 Tags are case sensitive, so <literal>$tag$String content$tag$</literal>
371 is correct, but <literal>$TAG$String content$tag$</literal> is not.
375 A dollar-quoted string that follows a keyword or identifier must
376 be separated from it by whitespace; otherwise the dollar quoting
377 delimiter would be taken as part of the preceding identifier.
381 Dollar quoting is not part of the SQL standard, but it is often a more
382 convenient way to write complicated string literals than the
383 standard-compliant single quote syntax. It is particularly useful when
384 representing string constants inside other constants, as is often needed
385 in procedural function definitions. With single-quote syntax, each
386 backslash in the above example would have to be written as four
387 backslashes, which would be reduced to two backslashes in parsing the
388 original string constant, and then to one when the inner string constant
389 is re-parsed during function execution.
393 <sect3 id="sql-syntax-bit-strings">
394 <title>Bit-String Constants</title>
396 <indexterm zone="sql-syntax-bit-strings">
397 <primary>bit string</primary>
398 <secondary>constant</secondary>
402 Bit-string constants look like regular string constants with a
403 <literal>B</literal> (upper or lower case) immediately before the
404 opening quote (no intervening whitespace), e.g.,
405 <literal>B'1001'</literal>. The only characters allowed within
406 bit-string constants are <literal>0</literal> and
407 <literal>1</literal>.
411 Alternatively, bit-string constants can be specified in hexadecimal
412 notation, using a leading <literal>X</literal> (upper or lower case),
413 e.g., <literal>X'1FF'</literal>. This notation is equivalent to
414 a bit-string constant with four binary digits for each hexadecimal digit.
418 Both forms of bit-string constant can be continued
419 across lines in the same way as regular string constants.
420 Dollar quoting cannot be used in a bit-string constant.
425 <title>Numeric Constants</title>
428 <primary>number</primary>
429 <secondary>constant</secondary>
433 Numeric constants are accepted in these general forms:
435 <replaceable>digits</replaceable>
436 <replaceable>digits</replaceable>.<optional><replaceable>digits</replaceable></optional><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
437 <optional><replaceable>digits</replaceable></optional>.<replaceable>digits</replaceable><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
438 <replaceable>digits</replaceable>e<optional>+-</optional><replaceable>digits</replaceable>
440 where <replaceable>digits</replaceable> is one or more decimal
441 digits (0 through 9). At least one digit must be before or after the
442 decimal point, if one is used. At least one digit must follow the
443 exponent marker (<literal>e</literal>), if one is present.
444 There may not be any spaces or other characters embedded in the
445 constant. Note that any leading plus or minus sign is not actually
446 considered part of the constant; it is an operator applied to the
451 These are some examples of valid numeric constants:
463 <indexterm><primary>integer</primary></indexterm>
464 <indexterm><primary>bigint</primary></indexterm>
465 <indexterm><primary>numeric</primary></indexterm>
466 A numeric constant that contains neither a decimal point nor an
467 exponent is initially presumed to be type <type>integer</> if its
468 value fits in type <type>integer</> (32 bits); otherwise it is
469 presumed to be type <type>bigint</> if its
470 value fits in type <type>bigint</> (64 bits); otherwise it is
471 taken to be type <type>numeric</>. Constants that contain decimal
472 points and/or exponents are always initially presumed to be type
477 The initially assigned data type of a numeric constant is just a
478 starting point for the type resolution algorithms. In most cases
479 the constant will be automatically coerced to the most
480 appropriate type depending on context. When necessary, you can
481 force a numeric value to be interpreted as a specific data type
482 by casting it.<indexterm><primary>type cast</primary></indexterm>
483 For example, you can force a numeric value to be treated as type
484 <type>real</> (<type>float4</>) by writing
487 REAL '1.23' -- string style
488 1.23::REAL -- PostgreSQL (historical) style
491 These are actually just special cases of the general casting
492 notations discussed next.
496 <sect3 id="sql-syntax-constants-generic">
497 <title>Constants of Other Types</title>
500 <primary>data type</primary>
501 <secondary>constant</secondary>
505 A constant of an <emphasis>arbitrary</emphasis> type can be
506 entered using any one of the following notations:
508 <replaceable>type</replaceable> '<replaceable>string</replaceable>'
509 '<replaceable>string</replaceable>'::<replaceable>type</replaceable>
510 CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
512 The string constant's text is passed to the input conversion
513 routine for the type called <replaceable>type</replaceable>. The
514 result is a constant of the indicated type. The explicit type
515 cast may be omitted if there is no ambiguity as to the type the
516 constant must be (for example, when it is assigned directly to a
517 table column), in which case it is automatically coerced.
521 The string constant can be written using either regular SQL
522 notation or dollar-quoting.
526 It is also possible to specify a type coercion using a function-like
529 <replaceable>typename</replaceable> ( '<replaceable>string</replaceable>' )
531 but not all type names may be used in this way; see <xref
532 linkend="sql-syntax-type-casts"> for details.
536 The <literal>::</literal>, <literal>CAST()</literal>, and
537 function-call syntaxes can also be used to specify run-time type
538 conversions of arbitrary expressions, as discussed in <xref
539 linkend="sql-syntax-type-casts">. But the form
540 <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal>
541 can only be used to specify the type of a literal constant.
542 Another restriction on
543 <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal>
544 is that it does not work for array types; use <literal>::</literal>
545 or <literal>CAST()</literal> to specify the type of an array constant.
549 The <literal>CAST()</> syntax conforms to SQL. The
550 <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal>
551 syntax is a generalization of the standard: SQL specifies this syntax only
552 for a few data types, but <productname>PostgreSQL</productname> allows it
553 for all types. The syntax with
554 <literal>::</literal> is historical <productname>PostgreSQL</productname>
555 usage, as is the function-call syntax.
560 <sect2 id="sql-syntax-operators">
561 <title>Operators</title>
563 <indexterm zone="sql-syntax-operators">
564 <primary>operator</primary>
565 <secondary>syntax</secondary>
569 An operator name is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
570 (63 by default) characters from the following list:
572 + - * / < > = ~ ! @ # % ^ & | ` ?
575 There are a few restrictions on operator names, however:
579 <literal>--</literal> and <literal>/*</literal> cannot appear
580 anywhere in an operator name, since they will be taken as the
587 A multiple-character operator name cannot end in <literal>+</> or <literal>-</>,
588 unless the name also contains at least one of these characters:
590 ~ ! @ # % ^ & | ` ?
592 For example, <literal>@-</literal> is an allowed operator name,
593 but <literal>*-</literal> is not. This restriction allows
594 <productname>PostgreSQL</productname> to parse SQL-compliant
595 queries without requiring spaces between tokens.
602 When working with non-SQL-standard operator names, you will usually
603 need to separate adjacent operators with spaces to avoid ambiguity.
604 For example, if you have defined a left unary operator named <literal>@</literal>,
605 you cannot write <literal>X*@Y</literal>; you must write
606 <literal>X* @Y</literal> to ensure that
607 <productname>PostgreSQL</productname> reads it as two operator names
613 <title>Special Characters</title>
616 Some characters that are not alphanumeric have a special meaning
617 that is different from being an operator. Details on the usage can
618 be found at the location where the respective syntax element is
619 described. This section only exists to advise the existence and
620 summarize the purposes of these characters.
625 A dollar sign (<literal>$</literal>) followed by digits is used
626 to represent a positional parameter in the body of a function
627 definition or a prepared statement. In other contexts the
628 dollar sign may be part of an identifier or a dollar-quoted string
635 Parentheses (<literal>()</literal>) have their usual meaning to
636 group expressions and enforce precedence. In some cases
637 parentheses are required as part of the fixed syntax of a
638 particular SQL command.
644 Brackets (<literal>[]</literal>) are used to select the elements
645 of an array. See <xref linkend="arrays"> for more information
652 Commas (<literal>,</literal>) are used in some syntactical
653 constructs to separate the elements of a list.
659 The semicolon (<literal>;</literal>) terminates an SQL command.
660 It cannot appear anywhere within a command, except within a
661 string constant or quoted identifier.
667 The colon (<literal>:</literal>) is used to select
668 <quote>slices</quote> from arrays. (See <xref
669 linkend="arrays">.) In certain SQL dialects (such as Embedded
670 SQL), the colon is used to prefix variable names.
676 The asterisk (<literal>*</literal>) is used in some contexts to denote
677 all the fields of a table row or composite value. It also
678 has a special meaning when used as the argument of the
679 <function>COUNT</function> aggregate function.
685 The period (<literal>.</literal>) is used in numeric
686 constants, and to separate schema, table, and column names.
694 <sect2 id="sql-syntax-comments">
695 <title>Comments</title>
697 <indexterm zone="sql-syntax-comments">
698 <primary>comment</primary>
699 <secondary sortas="SQL">in SQL</secondary>
703 A comment is an arbitrary sequence of characters beginning with
704 double dashes and extending to the end of the line, e.g.:
706 -- This is a standard SQL comment
711 Alternatively, C-style block comments can be used:
714 * with nesting: /* nested block comment */
717 where the comment begins with <literal>/*</literal> and extends to
718 the matching occurrence of <literal>*/</literal>. These block
719 comments nest, as specified in the SQL standard but unlike C, so that one can
720 comment out larger blocks of code that may contain existing block
725 A comment is removed from the input stream before further syntax
726 analysis and is effectively replaced by whitespace.
730 <sect2 id="sql-precedence">
731 <title>Lexical Precedence</title>
733 <indexterm zone="sql-precedence">
734 <primary>operator</primary>
735 <secondary>precedence</secondary>
739 <xref linkend="sql-precedence-table"> shows the precedence and
740 associativity of the operators in <productname>PostgreSQL</>.
741 Most operators have the same precedence and are left-associative.
742 The precedence and associativity of the operators is hard-wired
743 into the parser. This may lead to non-intuitive behavior; for
744 example the Boolean operators <literal><</> and
745 <literal>></> have a different precedence than the Boolean
746 operators <literal><=</> and <literal>>=</>. Also, you will
747 sometimes need to add parentheses when using combinations of
748 binary and unary operators. For instance
756 because the parser has no idea — until it is too late
757 — that <token>!</token> is defined as a postfix operator,
758 not an infix one. To get the desired behavior in this case, you
763 This is the price one pays for extensibility.
766 <table id="sql-precedence-table">
767 <title>Operator Precedence (decreasing)</title>
772 <entry>Operator/Element</entry>
773 <entry>Associativity</entry>
774 <entry>Description</entry>
780 <entry><token>.</token></entry>
782 <entry>table/column name separator</entry>
786 <entry><token>::</token></entry>
788 <entry><productname>PostgreSQL</productname>-style typecast</entry>
792 <entry><token>[</token> <token>]</token></entry>
794 <entry>array element selection</entry>
798 <entry><token>-</token></entry>
800 <entry>unary minus</entry>
804 <entry><token>^</token></entry>
806 <entry>exponentiation</entry>
810 <entry><token>*</token> <token>/</token> <token>%</token></entry>
812 <entry>multiplication, division, modulo</entry>
816 <entry><token>+</token> <token>-</token></entry>
818 <entry>addition, subtraction</entry>
822 <entry><token>IS</token></entry>
824 <entry><literal>IS TRUE</>, <literal>IS FALSE</>, <literal>IS UNKNOWN</>, <literal>IS NULL</></entry>
828 <entry><token>ISNULL</token></entry>
830 <entry>test for null</entry>
834 <entry><token>NOTNULL</token></entry>
836 <entry>test for not null</entry>
840 <entry>(any other)</entry>
842 <entry>all other native and user-defined operators</entry>
846 <entry><token>IN</token></entry>
848 <entry>set membership</entry>
852 <entry><token>BETWEEN</token></entry>
854 <entry>range containment</entry>
858 <entry><token>OVERLAPS</token></entry>
860 <entry>time interval overlap</entry>
864 <entry><token>LIKE</token> <token>ILIKE</token> <token>SIMILAR</token></entry>
866 <entry>string pattern matching</entry>
870 <entry><token><</token> <token>></token></entry>
872 <entry>less than, greater than</entry>
876 <entry><token>=</token></entry>
878 <entry>equality, assignment</entry>
882 <entry><token>NOT</token></entry>
884 <entry>logical negation</entry>
888 <entry><token>AND</token></entry>
890 <entry>logical conjunction</entry>
894 <entry><token>OR</token></entry>
896 <entry>logical disjunction</entry>
903 Note that the operator precedence rules also apply to user-defined
904 operators that have the same names as the built-in operators
905 mentioned above. For example, if you define a
906 <quote>+</quote> operator for some custom data type it will have
907 the same precedence as the built-in <quote>+</quote> operator, no
908 matter what yours does.
912 When a schema-qualified operator name is used in the
913 <literal>OPERATOR</> syntax, as for example in
915 SELECT 3 OPERATOR(pg_catalog.+) 4;
917 the <literal>OPERATOR</> construct is taken to have the default precedence
918 shown in <xref linkend="sql-precedence-table"> for <quote>any other</> operator. This is true no matter
919 which specific operator name appears inside <literal>OPERATOR()</>.
924 <sect1 id="sql-expressions">
925 <title>Value Expressions</title>
927 <indexterm zone="sql-expressions">
928 <primary>expression</primary>
929 <secondary>syntax</secondary>
932 <indexterm zone="sql-expressions">
933 <primary>value expression</primary>
937 <primary>scalar</primary>
938 <see>expression</see>
942 Value expressions are used in a variety of contexts, such
943 as in the target list of the <command>SELECT</command> command, as
944 new column values in <command>INSERT</command> or
945 <command>UPDATE</command>, or in search conditions in a number of
946 commands. The result of a value expression is sometimes called a
947 <firstterm>scalar</firstterm>, to distinguish it from the result of
948 a table expression (which is a table). Value expressions are
949 therefore also called <firstterm>scalar expressions</firstterm> (or
950 even simply <firstterm>expressions</firstterm>). The expression
951 syntax allows the calculation of values from primitive parts using
952 arithmetic, logical, set, and other operations.
956 A value expression is one of the following:
961 A constant or literal value.
973 A positional parameter reference, in the body of a function definition
974 or prepared statement.
980 A subscripted expression.
986 A field selection expression.
992 An operator invocation.
1004 An aggregate expression.
1022 An array constructor.
1034 Another value expression in parentheses, useful to group
1035 subexpressions and override
1036 precedence.<indexterm><primary>parenthesis</></>
1043 In addition to this list, there are a number of constructs that can
1044 be classified as an expression but do not follow any general syntax
1045 rules. These generally have the semantics of a function or
1046 operator and are explained in the appropriate location in <xref
1047 linkend="functions">. An example is the <literal>IS NULL</literal>
1052 We have already discussed constants in <xref
1053 linkend="sql-syntax-constants">. The following sections discuss
1054 the remaining options.
1058 <title>Column References</title>
1061 <primary>column reference</primary>
1065 A column can be referenced in the form
1067 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable>
1072 <replaceable>correlation</replaceable> is the name of a
1073 table (possibly qualified with a schema name), or an alias for a table
1074 defined by means of a <literal>FROM</literal> clause, or one of
1075 the key words <literal>NEW</literal> or <literal>OLD</literal>.
1076 (<literal>NEW</literal> and <literal>OLD</literal> can only appear in rewrite rules,
1077 while other correlation names can be used in any SQL statement.)
1078 The correlation name and separating dot may be omitted if the column name
1079 is unique across all the tables being used in the current query. (See also <xref linkend="queries">.)
1084 <title>Positional Parameters</title>
1087 <primary>parameter</primary>
1088 <secondary>syntax</secondary>
1092 <primary>$</primary>
1096 A positional parameter reference is used to indicate a value
1097 that is supplied externally to an SQL statement. Parameters are
1098 used in SQL function definitions and in prepared queries. Some
1099 client libraries also support specifying data values separately
1100 from the SQL command string, in which case parameters are used to
1101 refer to the out-of-line data values.
1102 The form of a parameter reference is:
1104 $<replaceable>number</replaceable>
1109 For example, consider the definition of a function,
1110 <function>dept</function>, as
1113 CREATE FUNCTION dept(text) RETURNS dept
1114 AS $$ SELECT * FROM dept WHERE name = $1 $$
1118 Here the <literal>$1</literal> references the value of the first
1119 function argument whenever the function is invoked.
1124 <title>Subscripts</title>
1127 <primary>subscript</primary>
1131 If an expression yields a value of an array type, then a specific
1132 element of the array value can be extracted by writing
1134 <replaceable>expression</replaceable>[<replaceable>subscript</replaceable>]
1136 or multiple adjacent elements (an <quote>array slice</>) can be extracted
1139 <replaceable>expression</replaceable>[<replaceable>lower_subscript</replaceable>:<replaceable>upper_subscript</replaceable>]
1141 (Here, the brackets <literal>[ ]</literal> are meant to appear literally.)
1142 Each <replaceable>subscript</replaceable> is itself an expression,
1143 which must yield an integer value.
1147 In general the array <replaceable>expression</replaceable> must be
1148 parenthesized, but the parentheses may be omitted when the expression
1149 to be subscripted is just a column reference or positional parameter.
1150 Also, multiple subscripts can be concatenated when the original array
1151 is multidimensional.
1155 mytable.arraycolumn[4]
1156 mytable.two_d_column[17][34]
1158 (arrayfunction(a,b))[42]
1161 The parentheses in the last example are required.
1162 See <xref linkend="arrays"> for more about arrays.
1167 <title>Field Selection</title>
1170 <primary>field selection</primary>
1174 If an expression yields a value of a composite type (row type), then a
1175 specific field of the row can be extracted by writing
1177 <replaceable>expression</replaceable>.<replaceable>fieldname</replaceable>
1182 In general the row <replaceable>expression</replaceable> must be
1183 parenthesized, but the parentheses may be omitted when the expression
1184 to be selected from is just a table reference or positional parameter.
1190 (rowfunction(a,b)).col3
1193 (Thus, a qualified column reference is actually just a special case
1194 of the field selection syntax.)
1199 <title>Operator Invocations</title>
1202 <primary>operator</primary>
1203 <secondary>invocation</secondary>
1207 There are three possible syntaxes for an operator invocation:
1209 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
1210 <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
1211 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
1213 where the <replaceable>operator</replaceable> token follows the syntax
1214 rules of <xref linkend="sql-syntax-operators">, or is one of the
1215 key words <token>AND</token>, <token>OR</token>, and
1216 <token>NOT</token>, or is a qualified operator name in the form
1218 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operatorname</><literal>)</>
1220 Which particular operators exist and whether
1221 they are unary or binary depends on what operators have been
1222 defined by the system or the user. <xref linkend="functions">
1223 describes the built-in operators.
1228 <title>Function Calls</title>
1231 <primary>function</primary>
1232 <secondary>invocation</secondary>
1236 The syntax for a function call is the name of a function
1237 (possibly qualified with a schema name), followed by its argument list
1238 enclosed in parentheses:
1241 <replaceable>function</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> )
1246 For example, the following computes the square root of 2:
1253 The list of built-in functions is in <xref linkend="functions">.
1254 Other functions may be added by the user.
1258 <sect2 id="syntax-aggregates">
1259 <title>Aggregate Expressions</title>
1261 <indexterm zone="syntax-aggregates">
1262 <primary>aggregate function</primary>
1263 <secondary>invocation</secondary>
1267 An <firstterm>aggregate expression</firstterm> represents the
1268 application of an aggregate function across the rows selected by a
1269 query. An aggregate function reduces multiple inputs to a single
1270 output value, such as the sum or average of the inputs. The
1271 syntax of an aggregate expression is one of the following:
1274 <replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>)
1275 <replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>)
1276 <replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)
1277 <replaceable>aggregate_name</replaceable> ( * )
1280 where <replaceable>aggregate_name</replaceable> is a previously
1281 defined aggregate (possibly qualified with a schema name), and
1282 <replaceable>expression</replaceable> is
1283 any value expression that does not itself contain an aggregate
1288 The first form of aggregate expression invokes the aggregate
1289 across all input rows for which the given expression yields a
1290 non-null value. (Actually, it is up to the aggregate function
1291 whether to ignore null values or not — but all the standard ones do.)
1292 The second form is the same as the first, since
1293 <literal>ALL</literal> is the default. The third form invokes the
1294 aggregate for all distinct non-null values of the expression found
1295 in the input rows. The last form invokes the aggregate once for
1296 each input row regardless of null or non-null values; since no
1297 particular input value is specified, it is generally only useful
1298 for the <function>count()</function> aggregate function.
1302 For example, <literal>count(*)</literal> yields the total number
1303 of input rows; <literal>count(f1)</literal> yields the number of
1304 input rows in which <literal>f1</literal> is non-null;
1305 <literal>count(distinct f1)</literal> yields the number of
1306 distinct non-null values of <literal>f1</literal>.
1310 The predefined aggregate functions are described in <xref
1311 linkend="functions-aggregate">. Other aggregate functions may be added
1316 An aggregate expression may only appear in the result list or
1317 <literal>HAVING</> clause of a <command>SELECT</> command.
1318 It is forbidden in other clauses, such as <literal>WHERE</>,
1319 because those clauses are logically evaluated before the results
1320 of aggregates are formed.
1324 When an aggregate expression appears in a subquery (see
1325 <xref linkend="sql-syntax-scalar-subqueries"> and
1326 <xref linkend="functions-subquery">), the aggregate is normally
1327 evaluated over the rows of the subquery. But an exception occurs
1328 if the aggregate's argument contains only outer-level variables:
1329 the aggregate then belongs to the nearest such outer level, and is
1330 evaluated over the rows of that query. The aggregate expression
1331 as a whole is then an outer reference for the subquery it appears in,
1332 and acts as a constant over any one evaluation of that subquery.
1333 The restriction about
1334 appearing only in the result list or <literal>HAVING</> clause
1335 applies with respect to the query level that the aggregate belongs to.
1339 <sect2 id="sql-syntax-type-casts">
1340 <title>Type Casts</title>
1343 <primary>data type</primary>
1344 <secondary>type cast</secondary>
1348 <primary>type cast</primary>
1352 A type cast specifies a conversion from one data type to another.
1353 <productname>PostgreSQL</productname> accepts two equivalent syntaxes
1356 CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> )
1357 <replaceable>expression</replaceable>::<replaceable>type</replaceable>
1359 The <literal>CAST</> syntax conforms to SQL; the syntax with
1360 <literal>::</literal> is historical <productname>PostgreSQL</productname>
1365 When a cast is applied to a value expression of a known type, it
1366 represents a run-time type conversion. The cast will succeed only
1367 if a suitable type conversion operation has been defined. Notice that this
1368 is subtly different from the use of casts with constants, as shown in
1369 <xref linkend="sql-syntax-constants-generic">. A cast applied to an
1370 unadorned string literal represents the initial assignment of a type
1371 to a literal constant value, and so it will succeed for any type
1372 (if the contents of the string literal are acceptable input syntax for the
1377 An explicit type cast may usually be omitted if there is no ambiguity as
1378 to the type that a value expression must produce (for example, when it is
1379 assigned to a table column); the system will automatically apply a
1380 type cast in such cases. However, automatic casting is only done for
1381 casts that are marked <quote>OK to apply implicitly</>
1382 in the system catalogs. Other casts must be invoked with
1383 explicit casting syntax. This restriction is intended to prevent
1384 surprising conversions from being applied silently.
1388 It is also possible to specify a type cast using a function-like
1391 <replaceable>typename</replaceable> ( <replaceable>expression</replaceable> )
1393 However, this only works for types whose names are also valid as
1394 function names. For example, <literal>double precision</literal>
1395 can't be used this way, but the equivalent <literal>float8</literal>
1396 can. Also, the names <literal>interval</>, <literal>time</>, and
1397 <literal>timestamp</> can only be used in this fashion if they are
1398 double-quoted, because of syntactic conflicts. Therefore, the use of
1399 the function-like cast syntax leads to inconsistencies and should
1400 probably be avoided in new applications.
1402 (The function-like syntax is in fact just a function call. When
1403 one of the two standard cast syntaxes is used to do a run-time
1404 conversion, it will internally invoke a registered function to
1405 perform the conversion. By convention, these conversion functions
1406 have the same name as their output type, and thus the <quote>function-like
1407 syntax</> is nothing more than a direct invocation of the underlying
1408 conversion function. Obviously, this is not something that a portable
1409 application should rely on.)
1413 <sect2 id="sql-syntax-scalar-subqueries">
1414 <title>Scalar Subqueries</title>
1417 <primary>subquery</primary>
1421 A scalar subquery is an ordinary
1422 <command>SELECT</command> query in parentheses that returns exactly one
1423 row with one column. (See <xref linkend="queries"> for information about writing queries.)
1424 The <command>SELECT</command> query is executed
1425 and the single returned value is used in the surrounding value expression.
1426 It is an error to use a query that
1427 returns more than one row or more than one column as a scalar subquery.
1428 (But if, during a particular execution, the subquery returns no rows,
1429 there is no error; the scalar result is taken to be null.)
1430 The subquery can refer to variables from the surrounding query,
1431 which will act as constants during any one evaluation of the subquery.
1432 See also <xref linkend="functions-subquery"> for other expressions involving subqueries.
1436 For example, the following finds the largest city population in each
1439 SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
1445 <sect2 id="sql-syntax-array-constructors">
1446 <title>Array Constructors</title>
1449 <primary>array</primary>
1450 <secondary>constructor</secondary>
1454 <primary>ARRAY</primary>
1458 An array constructor is an expression that builds an
1459 array value from values for its member elements. A simple array
1461 consists of the key word <literal>ARRAY</literal>, a left square bracket
1462 <literal>[</>, one or more expressions (separated by commas) for the
1463 array element values, and finally a right square bracket <literal>]</>.
1466 SELECT ARRAY[1,2,3+4];
1472 The array element type is the common type of the member expressions,
1473 determined using the same rules as for <literal>UNION</> or
1474 <literal>CASE</> constructs (see <xref linkend="typeconv-union-case">).
1478 Multidimensional array values can be built by nesting array
1480 In the inner constructors, the key word <literal>ARRAY</literal> may
1481 be omitted. For example, these produce the same result:
1484 SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
1490 SELECT ARRAY[[1,2],[3,4]];
1497 Since multidimensional arrays must be rectangular, inner constructors
1498 at the same level must produce sub-arrays of identical dimensions.
1502 Multidimensional array constructor elements can be anything yielding
1503 an array of the proper kind, not only a sub-<literal>ARRAY</> construct.
1506 CREATE TABLE arr(f1 int[], f2 int[]);
1508 INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);
1510 SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;
1512 ------------------------------------------------
1513 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
1519 It is also possible to construct an array from the results of a
1520 subquery. In this form, the array constructor is written with the
1521 key word <literal>ARRAY</literal> followed by a parenthesized (not
1522 bracketed) subquery. For example:
1524 SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
1526 -------------------------------------------------------------
1527 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
1530 The subquery must return a single column. The resulting
1531 one-dimensional array will have an element for each row in the
1532 subquery result, with an element type matching that of the
1533 subquery's output column.
1537 The subscripts of an array value built with <literal>ARRAY</literal>
1538 always begin with one. For more information about arrays, see
1539 <xref linkend="arrays">.
1544 <sect2 id="sql-syntax-row-constructors">
1545 <title>Row Constructors</title>
1548 <primary>composite type</primary>
1549 <secondary>constructor</secondary>
1553 <primary>row type</primary>
1554 <secondary>constructor</secondary>
1558 <primary>ROW</primary>
1562 A row constructor is an expression that builds a row value (also
1563 called a composite value) from values
1564 for its member fields. A row constructor consists of the key word
1565 <literal>ROW</literal>, a left parenthesis, zero or more
1566 expressions (separated by commas) for the row field values, and finally
1567 a right parenthesis. For example,
1569 SELECT ROW(1,2.5,'this is a test');
1571 The key word <literal>ROW</> is optional when there is more than one
1572 expression in the list.
1576 By default, the value created by a <literal>ROW</> expression is of
1577 an anonymous record type. If necessary, it can be cast to a named
1578 composite type — either the row type of a table, or a composite type
1579 created with <command>CREATE TYPE AS</>. An explicit cast may be needed
1580 to avoid ambiguity. For example:
1582 CREATE TABLE mytable(f1 int, f2 float, f3 text);
1584 CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;
1586 -- No cast needed since only one getf1() exists
1587 SELECT getf1(ROW(1,2.5,'this is a test'));
1593 CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
1595 CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;
1597 -- Now we need a cast to indicate which function to call:
1598 SELECT getf1(ROW(1,2.5,'this is a test'));
1599 ERROR: function getf1(record) is not unique
1601 SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
1607 SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
1616 Row constructors can be used to build composite values to be stored
1617 in a composite-type table column, or to be passed to a function that
1618 accepts a composite parameter. Also,
1619 it is possible to compare two row values or test a row with
1620 <literal>IS NULL</> or <literal>IS NOT NULL</>, for example
1622 SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');
1624 SELECT ROW(a, b, c) IS NOT NULL FROM table;
1626 For more detail see <xref linkend="functions-comparisons">.
1627 Row constructors can also be used in connection with subqueries,
1628 as discussed in <xref linkend="functions-subquery">.
1633 <sect2 id="syntax-express-eval">
1634 <title>Expression Evaluation Rules</title>
1637 <primary>expression</primary>
1638 <secondary>order of evaluation</secondary>
1642 The order of evaluation of subexpressions is not defined. In
1643 particular, the inputs of an operator or function are not necessarily
1644 evaluated left-to-right or in any other fixed order.
1648 Furthermore, if the result of an expression can be determined by
1649 evaluating only some parts of it, then other subexpressions
1650 might not be evaluated at all. For instance, if one wrote
1652 SELECT true OR somefunc();
1654 then <literal>somefunc()</literal> would (probably) not be called
1655 at all. The same would be the case if one wrote
1657 SELECT somefunc() OR true;
1659 Note that this is not the same as the left-to-right
1660 <quote>short-circuiting</quote> of Boolean operators that is found
1661 in some programming languages.
1665 As a consequence, it is unwise to use functions with side effects
1666 as part of complex expressions. It is particularly dangerous to
1667 rely on side effects or evaluation order in <literal>WHERE</> and <literal>HAVING</> clauses,
1668 since those clauses are extensively reprocessed as part of
1669 developing an execution plan. Boolean
1670 expressions (<literal>AND</>/<literal>OR</>/<literal>NOT</> combinations) in those clauses may be reorganized
1671 in any manner allowed by the laws of Boolean algebra.
1675 When it is essential to force evaluation order, a <literal>CASE</>
1676 construct (see <xref linkend="functions-conditional">) may be
1677 used. For example, this is an untrustworthy way of trying to
1678 avoid division by zero in a <literal>WHERE</> clause:
1680 SELECT ... WHERE x <> 0 AND y/x > 1.5;
1684 SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
1686 A <literal>CASE</> construct used in this fashion will defeat optimization
1687 attempts, so it should only be done when necessary. (In this particular
1688 example, it would doubtless be best to sidestep the problem by writing
1689 <literal>y > 1.5*x</> instead.)
1696 <!-- Keep this comment at the end of the file
1701 sgml-minimize-attributes:nil
1702 sgml-always-quote-attributes:t
1705 sgml-parent-document:nil
1706 sgml-default-dtd-file:"./reference.ced"
1707 sgml-exposed-tags:nil
1708 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1709 sgml-local-ecat-files:nil