1 <!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.150 2010/08/05 18:21:17 tgl Exp $ -->
3 <chapter id="sql-syntax">
4 <title>SQL Syntax</title>
6 <indexterm zone="sql-syntax">
7 <primary>syntax</primary>
8 <secondary>SQL</secondary>
12 This chapter describes the syntax of SQL. It forms the foundation
13 for understanding the following chapters which will go into detail
14 about how SQL commands are applied to define and modify data.
18 We also advise users who are already familiar with SQL to read this
19 chapter carefully because it contains several rules and concepts that
20 are implemented inconsistently among SQL databases or that are
21 specific to <productname>PostgreSQL</productname>.
24 <sect1 id="sql-syntax-lexical">
25 <title>Lexical Structure</title>
28 <primary>token</primary>
32 SQL input consists of a sequence of
33 <firstterm>commands</firstterm>. A command is composed of a
34 sequence of <firstterm>tokens</firstterm>, terminated by a
35 semicolon (<quote>;</quote>). The end of the input stream also
36 terminates a command. Which tokens are valid depends on the syntax
37 of the particular command.
41 A token can be a <firstterm>key word</firstterm>, an
42 <firstterm>identifier</firstterm>, a <firstterm>quoted
43 identifier</firstterm>, a <firstterm>literal</firstterm> (or
44 constant), or a special character symbol. Tokens are normally
45 separated by whitespace (space, tab, newline), but need not be if
46 there is no ambiguity (which is generally only the case if a
47 special character is adjacent to some other token type).
51 Additionally, <firstterm>comments</firstterm> can occur in SQL
52 input. They are not tokens, they are effectively equivalent to
57 For example, the following is (syntactically) valid SQL input:
59 SELECT * FROM MY_TABLE;
60 UPDATE MY_TABLE SET A = 5;
61 INSERT INTO MY_TABLE VALUES (3, 'hi there');
63 This is a sequence of three commands, one per line (although this
64 is not required; more than one command can be on a line, and
65 commands can usefully be split across lines).
69 The SQL syntax is not very consistent regarding what tokens
70 identify commands and which are operands or parameters. The first
71 few tokens are generally the command name, so in the above example
72 we would usually speak of a <quote>SELECT</quote>, an
73 <quote>UPDATE</quote>, and an <quote>INSERT</quote> command. But
74 for instance the <command>UPDATE</command> command always requires
75 a <token>SET</token> token to appear in a certain position, and
76 this particular variation of <command>INSERT</command> also
77 requires a <token>VALUES</token> in order to be complete. The
78 precise syntax rules for each command are described in <xref linkend="reference">.
81 <sect2 id="sql-syntax-identifiers">
82 <title>Identifiers and Key Words</title>
84 <indexterm zone="sql-syntax-identifiers">
85 <primary>identifier</primary>
86 <secondary>syntax of</secondary>
89 <indexterm zone="sql-syntax-identifiers">
90 <primary>name</primary>
91 <secondary>syntax of</secondary>
94 <indexterm zone="sql-syntax-identifiers">
95 <primary>key word</primary>
96 <secondary>syntax of</secondary>
100 Tokens such as <token>SELECT</token>, <token>UPDATE</token>, or
101 <token>VALUES</token> in the example above are examples of
102 <firstterm>key words</firstterm>, that is, words that have a fixed
103 meaning in the SQL language. The tokens <token>MY_TABLE</token>
104 and <token>A</token> are examples of
105 <firstterm>identifiers</firstterm>. They identify names of
106 tables, columns, or other database objects, depending on the
107 command they are used in. Therefore they are sometimes simply
108 called <quote>names</quote>. Key words and identifiers have the
109 same lexical structure, meaning that one cannot know whether a
110 token is an identifier or a key word without knowing the language.
111 A complete list of key words can be found in <xref
112 linkend="sql-keywords-appendix">.
116 SQL identifiers and key words must begin with a letter
117 (<literal>a</literal>-<literal>z</literal>, but also letters with
118 diacritical marks and non-Latin letters) or an underscore
119 (<literal>_</literal>). Subsequent characters in an identifier or
120 key word can be letters, underscores, digits
121 (<literal>0</literal>-<literal>9</literal>), or dollar signs
122 (<literal>$</>). Note that dollar signs are not allowed in identifiers
123 according to the letter of the SQL standard, so their use might render
124 applications less portable.
125 The SQL standard will not define a key word that contains
126 digits or starts or ends with an underscore, so identifiers of this
127 form are safe against possible conflict with future extensions of the
132 <indexterm><primary>identifier</primary><secondary>length</secondary></indexterm>
133 The system uses no more than <symbol>NAMEDATALEN</symbol>-1
134 bytes of an identifier; longer names can be written in
135 commands, but they will be truncated. By default,
136 <symbol>NAMEDATALEN</symbol> is 64 so the maximum identifier
137 length is 63 bytes. If this limit is problematic, it can be raised by
138 changing the <symbol>NAMEDATALEN</symbol> constant in
139 <filename>src/include/pg_config_manual.h</filename>.
144 <primary>case sensitivity</primary>
145 <secondary>of SQL commands</secondary>
147 Key words and unquoted identifiers are case insensitive. Therefore:
149 UPDATE MY_TABLE SET A = 5;
151 can equivalently be written as:
153 uPDaTE my_TabLE SeT a = 5;
155 A convention often used is to write key words in upper
156 case and names in lower case, e.g.:
158 UPDATE my_table SET a = 5;
164 <primary>quotation marks</primary>
165 <secondary>and identifiers</secondary>
167 There is a second kind of identifier: the <firstterm>delimited
168 identifier</firstterm> or <firstterm>quoted
169 identifier</firstterm>. It is formed by enclosing an arbitrary
170 sequence of characters in double-quotes
171 (<literal>"</literal>). <!-- " font-lock mania --> A delimited
172 identifier is always an identifier, never a key word. So
173 <literal>"select"</literal> could be used to refer to a column or
174 table named <quote>select</quote>, whereas an unquoted
175 <literal>select</literal> would be taken as a key word and
176 would therefore provoke a parse error when used where a table or
177 column name is expected. The example can be written with quoted
178 identifiers like this:
180 UPDATE "my_table" SET "a" = 5;
185 Quoted identifiers can contain any character, except the character
186 with code zero. (To include a double quote, write two double quotes.)
187 This allows constructing table or column names that would
188 otherwise not be possible, such as ones containing spaces or
189 ampersands. The length limitation still applies.
193 <indexterm><primary>Unicode escape</primary><secondary>in
194 identifiers</secondary></indexterm> A variant of quoted
195 identifiers allows including escaped Unicode characters identified
196 by their code points. This variant starts
197 with <literal>U&</literal> (upper or lower case U followed by
198 ampersand) immediately before the opening double quote, without
199 any spaces in between, for example <literal>U&"foo"</literal>.
200 (Note that this creates an ambiguity with the
201 operator <literal>&</literal>. Use spaces around the operator to
202 avoid this problem.) Inside the quotes, Unicode characters can be
203 specified in escaped form by writing a backslash followed by the
204 four-digit hexadecimal code point number or alternatively a
205 backslash followed by a plus sign followed by a six-digit
206 hexadecimal code point number. For example, the
207 identifier <literal>"data"</literal> could be written as
209 U&"d\0061t\+000061"
211 The following less trivial example writes the Russian
212 word <quote>slon</quote> (elephant) in Cyrillic letters:
214 U&"\0441\043B\043E\043D"
219 If a different escape character than backslash is desired, it can
221 the <literal>UESCAPE</literal><indexterm><primary>UESCAPE</primary></indexterm>
222 clause after the string, for example:
224 U&"d!0061t!+000061" UESCAPE '!'
226 The escape character can be any single character other than a
227 hexadecimal digit, the plus sign, a single quote, a double quote,
228 or a whitespace character. Note that the escape character is
229 written in single quotes, not double quotes.
233 To include the escape character in the identifier literally, write
238 The Unicode escape syntax works only when the server encoding is
239 UTF8. When other server encodings are used, only code points in
240 the ASCII range (up to <literal>\007F</literal>) can be specified.
241 Both the 4-digit and the 6-digit form can be used to specify
242 UTF-16 surrogate pairs to compose characters with code points
243 larger than U+FFFF (although the availability of
244 the 6-digit form technically makes this unnecessary).
248 Quoting an identifier also makes it case-sensitive, whereas
249 unquoted names are always folded to lower case. For example, the
250 identifiers <literal>FOO</literal>, <literal>foo</literal>, and
251 <literal>"foo"</literal> are considered the same by
252 <productname>PostgreSQL</productname>, but
253 <literal>"Foo"</literal> and <literal>"FOO"</literal> are
254 different from these three and each other. (The folding of
255 unquoted names to lower case in <productname>PostgreSQL</> is
256 incompatible with the SQL standard, which says that unquoted names
257 should be folded to upper case. Thus, <literal>foo</literal>
258 should be equivalent to <literal>"FOO"</literal> not
259 <literal>"foo"</literal> according to the standard. If you want
260 to write portable applications you are advised to always quote a
261 particular name or never quote it.)
266 <sect2 id="sql-syntax-constants">
267 <title>Constants</title>
269 <indexterm zone="sql-syntax-constants">
270 <primary>constant</primary>
274 There are three kinds of <firstterm>implicitly-typed
275 constants</firstterm> in <productname>PostgreSQL</productname>:
276 strings, bit strings, and numbers.
277 Constants can also be specified with explicit types, which can
278 enable more accurate representation and more efficient handling by
279 the system. These alternatives are discussed in the following
283 <sect3 id="sql-syntax-strings">
284 <title>String Constants</title>
286 <indexterm zone="sql-syntax-strings">
287 <primary>character string</primary>
288 <secondary>constant</secondary>
293 <primary>quotation marks</primary>
294 <secondary>escaping</secondary>
296 A string constant in SQL is an arbitrary sequence of characters
297 bounded by single quotes (<literal>'</literal>), for example
298 <literal>'This is a string'</literal>. To include
299 a single-quote character within a string constant,
300 write two adjacent single quotes, e.g.,
301 <literal>'Dianne''s horse'</literal>.
302 Note that this is <emphasis>not</> the same as a double-quote
303 character (<literal>"</>). <!-- font-lock sanity: " -->
307 Two string constants that are only separated by whitespace
308 <emphasis>with at least one newline</emphasis> are concatenated
309 and effectively treated as if the string had been written as one
310 constant. For example:
323 is not valid syntax. (This slightly bizarre behavior is specified
324 by <acronym>SQL</acronym>; <productname>PostgreSQL</productname> is
325 following the standard.)
329 <sect3 id="sql-syntax-strings-escape">
330 <title>String Constants with C-Style Escapes</title>
332 <indexterm zone="sql-syntax-strings-escape">
333 <primary>escape string syntax</primary>
335 <indexterm zone="sql-syntax-strings-escape">
336 <primary>backslash escapes</primary>
340 <productname>PostgreSQL</productname> also accepts <quote>escape</>
341 string constants, which are an extension to the SQL standard.
342 An escape string constant is specified by writing the letter
343 <literal>E</literal> (upper or lower case) just before the opening single
344 quote, e.g., <literal>E'foo'</>. (When continuing an escape string
345 constant across lines, write <literal>E</> only before the first opening
347 Within an escape string, a backslash character (<literal>\</>) begins a
348 C-like <firstterm>backslash escape</> sequence, in which the combination
349 of backslash and following character(s) represent a special byte
350 value, as shown in <xref linkend="sql-backslash-table">.
353 <table id="sql-backslash-table">
354 <title>Backslash Escape Sequences</title>
358 <entry>Backslash Escape Sequence</>
359 <entry>Interpretation</entry>
365 <entry><literal>\b</literal></entry>
366 <entry>backspace</entry>
369 <entry><literal>\f</literal></entry>
370 <entry>form feed</entry>
373 <entry><literal>\n</literal></entry>
374 <entry>newline</entry>
377 <entry><literal>\r</literal></entry>
378 <entry>carriage return</entry>
381 <entry><literal>\t</literal></entry>
386 <literal>\<replaceable>o</replaceable></literal>,
387 <literal>\<replaceable>oo</replaceable></literal>,
388 <literal>\<replaceable>ooo</replaceable></literal>
389 (<replaceable>o</replaceable> = 0 - 7)
391 <entry>octal byte value</entry>
395 <literal>\x<replaceable>h</replaceable></literal>,
396 <literal>\x<replaceable>hh</replaceable></literal>
397 (<replaceable>h</replaceable> = 0 - 9, A - F)
399 <entry>hexadecimal byte value</entry>
403 <literal>\u<replaceable>xxxx</replaceable></literal>,
404 <literal>\U<replaceable>xxxxxxxx</replaceable></literal>
405 (<replaceable>x</replaceable> = 0 - 9, A - F)
407 <entry>16 or 32-bit hexadecimal Unicode character value</entry>
415 character following a backslash is taken literally. Thus, to
416 include a backslash character, write two backslashes (<literal>\\</>).
417 Also, a single quote can be included in an escape string by writing
418 <literal>\'</literal>, in addition to the normal way of <literal>''</>.
422 It is your responsibility that the byte sequences you create,
423 especially when using the octal or hexadecimal escapes, compose
424 valid characters in the server character set encoding. When the
425 server encoding is UTF-8, then the Unicode escapes or the
426 alternative Unicode escape syntax, explained
427 in <xref linkend="sql-syntax-strings-uescape">, should be used
428 instead. (The alternative would be doing the UTF-8 encoding by
429 hand and writing out the bytes, which would be very cumbersome.)
433 The Unicode escape syntax works fully only when the server
434 encoding is UTF-8. When other server encodings are used, only
435 code points in the ASCII range (up to <literal>\u007F</>) can be
436 specified. Both the 4-digit and the 8-digit form can be used to
437 specify UTF-16 surrogate pairs to compose characters with code
438 points larger than U+FFFF (although the
439 availability of the 8-digit form technically makes this
445 If the configuration parameter
446 <xref linkend="guc-standard-conforming-strings"> is <literal>off</>,
447 then <productname>PostgreSQL</productname> recognizes backslash escapes
448 in both regular and escape string constants. However, as of
449 <productname>PostgreSQL</> 9.1, the default is <literal>on</>, meaning
450 that backslash escapes are recognized only in escape string constants.
451 This behavior is more standards-compliant, but might break applications
452 which rely on the historical behavior, where backslash escapes
453 were always recognized. As a workaround, you can set this parameter
454 to <literal>off</>, but it is better to migrate away from using backslash
455 escapes. If you need to use a backslash escape to represent a special
456 character, write the string constant with an <literal>E</>.
460 In addition to <varname>standard_conforming_strings</>, the configuration
461 parameters <xref linkend="guc-escape-string-warning"> and
462 <xref linkend="guc-backslash-quote"> govern treatment of backslashes
468 The character with the code zero cannot be in a string constant.
472 <sect3 id="sql-syntax-strings-uescape">
473 <title>String Constants with Unicode Escapes</title>
475 <indexterm zone="sql-syntax-strings-uescape">
476 <primary>Unicode escape</primary>
477 <secondary>in string constants</secondary>
481 <productname>PostgreSQL</productname> also supports another type
482 of escape syntax for strings that allows specifying arbitrary
483 Unicode characters by code point. A Unicode escape string
484 constant starts with <literal>U&</literal> (upper or lower case
485 letter U followed by ampersand) immediately before the opening
486 quote, without any spaces in between, for
487 example <literal>U&'foo'</literal>. (Note that this creates an
488 ambiguity with the operator <literal>&</literal>. Use spaces
489 around the operator to avoid this problem.) Inside the quotes,
490 Unicode characters can be specified in escaped form by writing a
491 backslash followed by the four-digit hexadecimal code point
492 number or alternatively a backslash followed by a plus sign
493 followed by a six-digit hexadecimal code point number. For
494 example, the string <literal>'data'</literal> could be written as
496 U&'d\0061t\+000061'
498 The following less trivial example writes the Russian
499 word <quote>slon</quote> (elephant) in Cyrillic letters:
501 U&'\0441\043B\043E\043D'
506 If a different escape character than backslash is desired, it can
508 the <literal>UESCAPE</literal><indexterm><primary>UESCAPE</primary></indexterm>
509 clause after the string, for example:
511 U&'d!0061t!+000061' UESCAPE '!'
513 The escape character can be any single character other than a
514 hexadecimal digit, the plus sign, a single quote, a double quote,
515 or a whitespace character.
519 The Unicode escape syntax works only when the server encoding is
520 UTF8. When other server encodings are used, only code points in
521 the ASCII range (up to <literal>\007F</literal>) can be
523 Both the 4-digit and the 6-digit form can be used to specify
524 UTF-16 surrogate pairs to compose characters with code points
525 larger than U+FFFF (although the availability
526 of the 6-digit form technically makes this unnecessary).
530 Also, the Unicode escape syntax for string constants only works
531 when the configuration
532 parameter <xref linkend="guc-standard-conforming-strings"> is
533 turned on. This is because otherwise this syntax could confuse
534 clients that parse the SQL statements to the point that it could
535 lead to SQL injections and similar security issues. If the
536 parameter is set to off, this syntax will be rejected with an
541 To include the escape character in the string literally, write it
546 <sect3 id="sql-syntax-dollar-quoting">
547 <title>Dollar-Quoted String Constants</title>
550 <primary>dollar quoting</primary>
554 While the standard syntax for specifying string constants is usually
555 convenient, it can be difficult to understand when the desired string
556 contains many single quotes or backslashes, since each of those must
557 be doubled. To allow more readable queries in such situations,
558 <productname>PostgreSQL</productname> provides another way, called
559 <quote>dollar quoting</quote>, to write string constants.
560 A dollar-quoted string constant
561 consists of a dollar sign (<literal>$</literal>), an optional
562 <quote>tag</quote> of zero or more characters, another dollar
563 sign, an arbitrary sequence of characters that makes up the
564 string content, a dollar sign, the same tag that began this
565 dollar quote, and a dollar sign. For example, here are two
566 different ways to specify the string <quote>Dianne's horse</>
567 using dollar quoting:
570 $SomeTag$Dianne's horse$SomeTag$
572 Notice that inside the dollar-quoted string, single quotes can be
573 used without needing to be escaped. Indeed, no characters inside
574 a dollar-quoted string are ever escaped: the string content is always
575 written literally. Backslashes are not special, and neither are
576 dollar signs, unless they are part of a sequence matching the opening
581 It is possible to nest dollar-quoted string constants by choosing
582 different tags at each nesting level. This is most commonly used in
583 writing function definitions. For example:
587 RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
591 Here, the sequence <literal>$q$[\t\r\n\v\\]$q$</> represents a
592 dollar-quoted literal string <literal>[\t\r\n\v\\]</>, which will
593 be recognized when the function body is executed by
594 <productname>PostgreSQL</>. But since the sequence does not match
595 the outer dollar quoting delimiter <literal>$function$</>, it is
596 just some more characters within the constant so far as the outer
601 The tag, if any, of a dollar-quoted string follows the same rules
602 as an unquoted identifier, except that it cannot contain a dollar sign.
603 Tags are case sensitive, so <literal>$tag$String content$tag$</literal>
604 is correct, but <literal>$TAG$String content$tag$</literal> is not.
608 A dollar-quoted string that follows a keyword or identifier must
609 be separated from it by whitespace; otherwise the dollar quoting
610 delimiter would be taken as part of the preceding identifier.
614 Dollar quoting is not part of the SQL standard, but it is often a more
615 convenient way to write complicated string literals than the
616 standard-compliant single quote syntax. It is particularly useful when
617 representing string constants inside other constants, as is often needed
618 in procedural function definitions. With single-quote syntax, each
619 backslash in the above example would have to be written as four
620 backslashes, which would be reduced to two backslashes in parsing the
621 original string constant, and then to one when the inner string constant
622 is re-parsed during function execution.
626 <sect3 id="sql-syntax-bit-strings">
627 <title>Bit-String Constants</title>
629 <indexterm zone="sql-syntax-bit-strings">
630 <primary>bit string</primary>
631 <secondary>constant</secondary>
635 Bit-string constants look like regular string constants with a
636 <literal>B</literal> (upper or lower case) immediately before the
637 opening quote (no intervening whitespace), e.g.,
638 <literal>B'1001'</literal>. The only characters allowed within
639 bit-string constants are <literal>0</literal> and
640 <literal>1</literal>.
644 Alternatively, bit-string constants can be specified in hexadecimal
645 notation, using a leading <literal>X</literal> (upper or lower case),
646 e.g., <literal>X'1FF'</literal>. This notation is equivalent to
647 a bit-string constant with four binary digits for each hexadecimal digit.
651 Both forms of bit-string constant can be continued
652 across lines in the same way as regular string constants.
653 Dollar quoting cannot be used in a bit-string constant.
658 <title>Numeric Constants</title>
661 <primary>number</primary>
662 <secondary>constant</secondary>
666 Numeric constants are accepted in these general forms:
668 <replaceable>digits</replaceable>
669 <replaceable>digits</replaceable>.<optional><replaceable>digits</replaceable></optional><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
670 <optional><replaceable>digits</replaceable></optional>.<replaceable>digits</replaceable><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
671 <replaceable>digits</replaceable>e<optional>+-</optional><replaceable>digits</replaceable>
673 where <replaceable>digits</replaceable> is one or more decimal
674 digits (0 through 9). At least one digit must be before or after the
675 decimal point, if one is used. At least one digit must follow the
676 exponent marker (<literal>e</literal>), if one is present.
677 There cannot be any spaces or other characters embedded in the
678 constant. Note that any leading plus or minus sign is not actually
679 considered part of the constant; it is an operator applied to the
684 These are some examples of valid numeric constants:
696 <indexterm><primary>integer</primary></indexterm>
697 <indexterm><primary>bigint</primary></indexterm>
698 <indexterm><primary>numeric</primary></indexterm>
699 A numeric constant that contains neither a decimal point nor an
700 exponent is initially presumed to be type <type>integer</> if its
701 value fits in type <type>integer</> (32 bits); otherwise it is
702 presumed to be type <type>bigint</> if its
703 value fits in type <type>bigint</> (64 bits); otherwise it is
704 taken to be type <type>numeric</>. Constants that contain decimal
705 points and/or exponents are always initially presumed to be type
710 The initially assigned data type of a numeric constant is just a
711 starting point for the type resolution algorithms. In most cases
712 the constant will be automatically coerced to the most
713 appropriate type depending on context. When necessary, you can
714 force a numeric value to be interpreted as a specific data type
715 by casting it.<indexterm><primary>type cast</primary></indexterm>
716 For example, you can force a numeric value to be treated as type
717 <type>real</> (<type>float4</>) by writing:
720 REAL '1.23' -- string style
721 1.23::REAL -- PostgreSQL (historical) style
724 These are actually just special cases of the general casting
725 notations discussed next.
729 <sect3 id="sql-syntax-constants-generic">
730 <title>Constants of Other Types</title>
733 <primary>data type</primary>
734 <secondary>constant</secondary>
738 A constant of an <emphasis>arbitrary</emphasis> type can be
739 entered using any one of the following notations:
741 <replaceable>type</replaceable> '<replaceable>string</replaceable>'
742 '<replaceable>string</replaceable>'::<replaceable>type</replaceable>
743 CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
745 The string constant's text is passed to the input conversion
746 routine for the type called <replaceable>type</replaceable>. The
747 result is a constant of the indicated type. The explicit type
748 cast can be omitted if there is no ambiguity as to the type the
749 constant must be (for example, when it is assigned directly to a
750 table column), in which case it is automatically coerced.
754 The string constant can be written using either regular SQL
755 notation or dollar-quoting.
759 It is also possible to specify a type coercion using a function-like
762 <replaceable>typename</replaceable> ( '<replaceable>string</replaceable>' )
764 but not all type names can be used in this way; see <xref
765 linkend="sql-syntax-type-casts"> for details.
769 The <literal>::</literal>, <literal>CAST()</literal>, and
770 function-call syntaxes can also be used to specify run-time type
771 conversions of arbitrary expressions, as discussed in <xref
772 linkend="sql-syntax-type-casts">. To avoid syntactic ambiguity, the
773 <literal><replaceable>type</> '<replaceable>string</>'</literal>
774 syntax can only be used to specify the type of a simple literal constant.
775 Another restriction on the
776 <literal><replaceable>type</> '<replaceable>string</>'</literal>
777 syntax is that it does not work for array types; use <literal>::</literal>
778 or <literal>CAST()</literal> to specify the type of an array constant.
782 The <literal>CAST()</> syntax conforms to SQL. The
783 <literal><replaceable>type</> '<replaceable>string</>'</literal>
784 syntax is a generalization of the standard: SQL specifies this syntax only
785 for a few data types, but <productname>PostgreSQL</productname> allows it
786 for all types. The syntax with
787 <literal>::</literal> is historical <productname>PostgreSQL</productname>
788 usage, as is the function-call syntax.
793 <sect2 id="sql-syntax-operators">
794 <title>Operators</title>
796 <indexterm zone="sql-syntax-operators">
797 <primary>operator</primary>
798 <secondary>syntax</secondary>
802 An operator name is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
803 (63 by default) characters from the following list:
805 + - * / < > = ~ ! @ # % ^ & | ` ?
808 There are a few restrictions on operator names, however:
812 <literal>--</literal> and <literal>/*</literal> cannot appear
813 anywhere in an operator name, since they will be taken as the
820 A multiple-character operator name cannot end in <literal>+</> or <literal>-</>,
821 unless the name also contains at least one of these characters:
823 ~ ! @ # % ^ & | ` ?
825 For example, <literal>@-</literal> is an allowed operator name,
826 but <literal>*-</literal> is not. This restriction allows
827 <productname>PostgreSQL</productname> to parse SQL-compliant
828 queries without requiring spaces between tokens.
835 When working with non-SQL-standard operator names, you will usually
836 need to separate adjacent operators with spaces to avoid ambiguity.
837 For example, if you have defined a left unary operator named <literal>@</literal>,
838 you cannot write <literal>X*@Y</literal>; you must write
839 <literal>X* @Y</literal> to ensure that
840 <productname>PostgreSQL</productname> reads it as two operator names
846 <title>Special Characters</title>
849 Some characters that are not alphanumeric have a special meaning
850 that is different from being an operator. Details on the usage can
851 be found at the location where the respective syntax element is
852 described. This section only exists to advise the existence and
853 summarize the purposes of these characters.
858 A dollar sign (<literal>$</literal>) followed by digits is used
859 to represent a positional parameter in the body of a function
860 definition or a prepared statement. In other contexts the
861 dollar sign can be part of an identifier or a dollar-quoted string
868 Parentheses (<literal>()</literal>) have their usual meaning to
869 group expressions and enforce precedence. In some cases
870 parentheses are required as part of the fixed syntax of a
871 particular SQL command.
877 Brackets (<literal>[]</literal>) are used to select the elements
878 of an array. See <xref linkend="arrays"> for more information
885 Commas (<literal>,</literal>) are used in some syntactical
886 constructs to separate the elements of a list.
892 The semicolon (<literal>;</literal>) terminates an SQL command.
893 It cannot appear anywhere within a command, except within a
894 string constant or quoted identifier.
900 The colon (<literal>:</literal>) is used to select
901 <quote>slices</quote> from arrays. (See <xref
902 linkend="arrays">.) In certain SQL dialects (such as Embedded
903 SQL), the colon is used to prefix variable names.
909 The asterisk (<literal>*</literal>) is used in some contexts to denote
910 all the fields of a table row or composite value. It also
911 has a special meaning when used as the argument of an
912 aggregate function, namely that the aggregate does not require
913 any explicit parameter.
919 The period (<literal>.</literal>) is used in numeric
920 constants, and to separate schema, table, and column names.
928 <sect2 id="sql-syntax-comments">
929 <title>Comments</title>
931 <indexterm zone="sql-syntax-comments">
932 <primary>comment</primary>
933 <secondary sortas="SQL">in SQL</secondary>
937 A comment is a sequence of characters beginning with
938 double dashes and extending to the end of the line, e.g.:
940 -- This is a standard SQL comment
945 Alternatively, C-style block comments can be used:
948 * with nesting: /* nested block comment */
951 where the comment begins with <literal>/*</literal> and extends to
952 the matching occurrence of <literal>*/</literal>. These block
953 comments nest, as specified in the SQL standard but unlike C, so that one can
954 comment out larger blocks of code that might contain existing block
959 A comment is removed from the input stream before further syntax
960 analysis and is effectively replaced by whitespace.
964 <sect2 id="sql-precedence">
965 <title>Lexical Precedence</title>
967 <indexterm zone="sql-precedence">
968 <primary>operator</primary>
969 <secondary>precedence</secondary>
973 <xref linkend="sql-precedence-table"> shows the precedence and
974 associativity of the operators in <productname>PostgreSQL</>.
975 Most operators have the same precedence and are left-associative.
976 The precedence and associativity of the operators is hard-wired
977 into the parser. This can lead to non-intuitive behavior; for
978 example the Boolean operators <literal><</> and
979 <literal>></> have a different precedence than the Boolean
980 operators <literal><=</> and <literal>>=</>. Also, you will
981 sometimes need to add parentheses when using combinations of
982 binary and unary operators. For instance:
990 because the parser has no idea — until it is too late
991 — that <token>!</token> is defined as a postfix operator,
992 not an infix one. To get the desired behavior in this case, you
997 This is the price one pays for extensibility.
1000 <table id="sql-precedence-table">
1001 <title>Operator Precedence (decreasing)</title>
1006 <entry>Operator/Element</entry>
1007 <entry>Associativity</entry>
1008 <entry>Description</entry>
1014 <entry><token>.</token></entry>
1016 <entry>table/column name separator</entry>
1020 <entry><token>::</token></entry>
1022 <entry><productname>PostgreSQL</productname>-style typecast</entry>
1026 <entry><token>[</token> <token>]</token></entry>
1028 <entry>array element selection</entry>
1032 <entry><token>-</token></entry>
1033 <entry>right</entry>
1034 <entry>unary minus</entry>
1038 <entry><token>^</token></entry>
1040 <entry>exponentiation</entry>
1044 <entry><token>*</token> <token>/</token> <token>%</token></entry>
1046 <entry>multiplication, division, modulo</entry>
1050 <entry><token>+</token> <token>-</token></entry>
1052 <entry>addition, subtraction</entry>
1056 <entry><token>IS</token></entry>
1058 <entry><literal>IS TRUE</>, <literal>IS FALSE</>, <literal>IS UNKNOWN</>, <literal>IS NULL</></entry>
1062 <entry><token>ISNULL</token></entry>
1064 <entry>test for null</entry>
1068 <entry><token>NOTNULL</token></entry>
1070 <entry>test for not null</entry>
1074 <entry>(any other)</entry>
1076 <entry>all other native and user-defined operators</entry>
1080 <entry><token>IN</token></entry>
1082 <entry>set membership</entry>
1086 <entry><token>BETWEEN</token></entry>
1088 <entry>range containment</entry>
1092 <entry><token>OVERLAPS</token></entry>
1094 <entry>time interval overlap</entry>
1098 <entry><token>LIKE</token> <token>ILIKE</token> <token>SIMILAR</token></entry>
1100 <entry>string pattern matching</entry>
1104 <entry><token><</token> <token>></token></entry>
1106 <entry>less than, greater than</entry>
1110 <entry><token>=</token></entry>
1111 <entry>right</entry>
1112 <entry>equality, assignment</entry>
1116 <entry><token>NOT</token></entry>
1117 <entry>right</entry>
1118 <entry>logical negation</entry>
1122 <entry><token>AND</token></entry>
1124 <entry>logical conjunction</entry>
1128 <entry><token>OR</token></entry>
1130 <entry>logical disjunction</entry>
1137 Note that the operator precedence rules also apply to user-defined
1138 operators that have the same names as the built-in operators
1139 mentioned above. For example, if you define a
1140 <quote>+</quote> operator for some custom data type it will have
1141 the same precedence as the built-in <quote>+</quote> operator, no
1142 matter what yours does.
1146 When a schema-qualified operator name is used in the
1147 <literal>OPERATOR</> syntax, as for example in:
1149 SELECT 3 OPERATOR(pg_catalog.+) 4;
1151 the <literal>OPERATOR</> construct is taken to have the default precedence
1152 shown in <xref linkend="sql-precedence-table"> for <quote>any other</> operator. This is true no matter
1153 which specific operator appears inside <literal>OPERATOR()</>.
1158 <sect1 id="sql-expressions">
1159 <title>Value Expressions</title>
1161 <indexterm zone="sql-expressions">
1162 <primary>expression</primary>
1163 <secondary>syntax</secondary>
1166 <indexterm zone="sql-expressions">
1167 <primary>value expression</primary>
1171 <primary>scalar</primary>
1172 <see>expression</see>
1176 Value expressions are used in a variety of contexts, such
1177 as in the target list of the <command>SELECT</command> command, as
1178 new column values in <command>INSERT</command> or
1179 <command>UPDATE</command>, or in search conditions in a number of
1180 commands. The result of a value expression is sometimes called a
1181 <firstterm>scalar</firstterm>, to distinguish it from the result of
1182 a table expression (which is a table). Value expressions are
1183 therefore also called <firstterm>scalar expressions</firstterm> (or
1184 even simply <firstterm>expressions</firstterm>). The expression
1185 syntax allows the calculation of values from primitive parts using
1186 arithmetic, logical, set, and other operations.
1190 A value expression is one of the following:
1195 A constant or literal value
1207 A positional parameter reference, in the body of a function definition
1208 or prepared statement
1214 A subscripted expression
1220 A field selection expression
1226 An operator invocation
1238 An aggregate expression
1244 A window function call
1262 An array constructor
1274 Another value expression in parentheses (used to group
1275 subexpressions and override
1276 precedence<indexterm><primary>parenthesis</></>)
1283 In addition to this list, there are a number of constructs that can
1284 be classified as an expression but do not follow any general syntax
1285 rules. These generally have the semantics of a function or
1286 operator and are explained in the appropriate location in <xref
1287 linkend="functions">. An example is the <literal>IS NULL</literal>
1292 We have already discussed constants in <xref
1293 linkend="sql-syntax-constants">. The following sections discuss
1294 the remaining options.
1298 <title>Column References</title>
1301 <primary>column reference</primary>
1305 A column can be referenced in the form:
1307 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable>
1312 <replaceable>correlation</replaceable> is the name of a
1313 table (possibly qualified with a schema name), or an alias for a table
1314 defined by means of a <literal>FROM</literal> clause.
1315 The correlation name and separating dot can be omitted if the column name
1316 is unique across all the tables being used in the current query. (See also <xref linkend="queries">.)
1321 <title>Positional Parameters</title>
1324 <primary>parameter</primary>
1325 <secondary>syntax</secondary>
1329 <primary>$</primary>
1333 A positional parameter reference is used to indicate a value
1334 that is supplied externally to an SQL statement. Parameters are
1335 used in SQL function definitions and in prepared queries. Some
1336 client libraries also support specifying data values separately
1337 from the SQL command string, in which case parameters are used to
1338 refer to the out-of-line data values.
1339 The form of a parameter reference is:
1341 $<replaceable>number</replaceable>
1346 For example, consider the definition of a function,
1347 <function>dept</function>, as:
1350 CREATE FUNCTION dept(text) RETURNS dept
1351 AS $$ SELECT * FROM dept WHERE name = $1 $$
1355 Here the <literal>$1</literal> references the value of the first
1356 function argument whenever the function is invoked.
1361 <title>Subscripts</title>
1364 <primary>subscript</primary>
1368 If an expression yields a value of an array type, then a specific
1369 element of the array value can be extracted by writing
1371 <replaceable>expression</replaceable>[<replaceable>subscript</replaceable>]
1373 or multiple adjacent elements (an <quote>array slice</>) can be extracted
1376 <replaceable>expression</replaceable>[<replaceable>lower_subscript</replaceable>:<replaceable>upper_subscript</replaceable>]
1378 (Here, the brackets <literal>[ ]</literal> are meant to appear literally.)
1379 Each <replaceable>subscript</replaceable> is itself an expression,
1380 which must yield an integer value.
1384 In general the array <replaceable>expression</replaceable> must be
1385 parenthesized, but the parentheses can be omitted when the expression
1386 to be subscripted is just a column reference or positional parameter.
1387 Also, multiple subscripts can be concatenated when the original array
1388 is multidimensional.
1392 mytable.arraycolumn[4]
1393 mytable.two_d_column[17][34]
1395 (arrayfunction(a,b))[42]
1398 The parentheses in the last example are required.
1399 See <xref linkend="arrays"> for more about arrays.
1404 <title>Field Selection</title>
1407 <primary>field selection</primary>
1411 If an expression yields a value of a composite type (row type), then a
1412 specific field of the row can be extracted by writing
1414 <replaceable>expression</replaceable>.<replaceable>fieldname</replaceable>
1419 In general the row <replaceable>expression</replaceable> must be
1420 parenthesized, but the parentheses can be omitted when the expression
1421 to be selected from is just a table reference or positional parameter.
1427 (rowfunction(a,b)).col3
1430 (Thus, a qualified column reference is actually just a special case
1431 of the field selection syntax.) An important special case is
1432 extracting a field from a table column that is of a composite type:
1435 (compositecol).somefield
1436 (mytable.compositecol).somefield
1439 The parentheses are required here to show that
1440 <structfield>compositecol</> is a column name not a table name,
1441 or that <structname>mytable</> is a table name not a schema name
1447 <title>Operator Invocations</title>
1450 <primary>operator</primary>
1451 <secondary>invocation</secondary>
1455 There are three possible syntaxes for an operator invocation:
1457 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
1458 <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
1459 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
1461 where the <replaceable>operator</replaceable> token follows the syntax
1462 rules of <xref linkend="sql-syntax-operators">, or is one of the
1463 key words <token>AND</token>, <token>OR</token>, and
1464 <token>NOT</token>, or is a qualified operator name in the form:
1466 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operatorname</><literal>)</>
1468 Which particular operators exist and whether
1469 they are unary or binary depends on what operators have been
1470 defined by the system or the user. <xref linkend="functions">
1471 describes the built-in operators.
1476 <title>Function Calls</title>
1479 <primary>function</primary>
1480 <secondary>invocation</secondary>
1484 The syntax for a function call is the name of a function
1485 (possibly qualified with a schema name), followed by its argument list
1486 enclosed in parentheses:
1489 <replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> )
1494 For example, the following computes the square root of 2:
1501 The list of built-in functions is in <xref linkend="functions">.
1502 Other functions can be added by the user.
1506 The arguments can optionally have names attached.
1507 See <xref linkend="sql-syntax-calling-funcs"> for details.
1511 <sect2 id="syntax-aggregates">
1512 <title>Aggregate Expressions</title>
1514 <indexterm zone="syntax-aggregates">
1515 <primary>aggregate function</primary>
1516 <secondary>invocation</secondary>
1520 An <firstterm>aggregate expression</firstterm> represents the
1521 application of an aggregate function across the rows selected by a
1522 query. An aggregate function reduces multiple inputs to a single
1523 output value, such as the sum or average of the inputs. The
1524 syntax of an aggregate expression is one of the following:
1527 <replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
1528 <replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
1529 <replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
1530 <replaceable>aggregate_name</replaceable> ( * )
1533 where <replaceable>aggregate_name</replaceable> is a previously
1534 defined aggregate (possibly qualified with a schema name),
1535 <replaceable>expression</replaceable> is
1536 any value expression that does not itself contain an aggregate
1537 expression or a window function call, and
1538 <replaceable>order_by_clause</replaceable> is a optional
1539 <literal>ORDER BY</> clause as described below.
1543 The first form of aggregate expression invokes the aggregate
1544 across all input rows for which the given expression(s) yield
1545 non-null values. (Actually, it is up to the aggregate function
1546 whether to ignore null values or not — but all the standard ones do.)
1547 The second form is the same as the first, since
1548 <literal>ALL</literal> is the default. The third form invokes the
1549 aggregate for all distinct values of the expressions found
1550 in the input rows (ignoring nulls if the function chooses to do so).
1551 The last form invokes the aggregate once for
1552 each input row regardless of null or non-null values; since no
1553 particular input value is specified, it is generally only useful
1554 for the <function>count(*)</function> aggregate function.
1558 For example, <literal>count(*)</literal> yields the total number
1559 of input rows; <literal>count(f1)</literal> yields the number of
1560 input rows in which <literal>f1</literal> is non-null;
1561 <literal>count(distinct f1)</literal> yields the number of
1562 distinct non-null values of <literal>f1</literal>.
1566 Ordinarily, the input rows are fed to the aggregate function in an
1567 unspecified order. In many cases this does not matter; for example,
1568 <function>min</> produces the same result no matter what order it
1569 receives the inputs in. However, some aggregate functions
1570 (such as <function>array_agg</> and <function>string_agg</>) produce
1571 results that depend on the ordering of the input rows. When using
1572 such an aggregate, the optional <replaceable>order_by_clause</> can be
1573 used to specify the desired ordering. The <replaceable>order_by_clause</>
1574 has the same syntax as for a query-level <literal>ORDER BY</> clause, as
1575 described in <xref linkend="queries-order">, except that its expressions
1576 are always just expressions and cannot be output-column names or numbers.
1579 SELECT array_agg(a ORDER BY b DESC) FROM table;
1584 When dealing with multiple-argument aggregate functions, note that the
1585 <literal>ORDER BY</> clause goes after all the aggregate arguments.
1586 For example, write this:
1588 SELECT string_agg(a, ',' ORDER BY a) FROM table;
1592 SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
1594 The latter is syntactically valid, but it represents a call of a
1595 single-argument aggregate function with two <literal>ORDER BY</> keys
1596 (the second one being rather useless since it's a constant).
1600 If <literal>DISTINCT</> is specified in addition to an
1601 <replaceable>order_by_clause</>, then all the <literal>ORDER BY</>
1602 expressions must match regular arguments of the aggregate; that is,
1603 you cannot sort on an expression that is not included in the
1604 <literal>DISTINCT</> list.
1609 The ability to specify both <literal>DISTINCT</> and <literal>ORDER BY</>
1610 in an aggregate function is a <productname>PostgreSQL</> extension.
1615 The predefined aggregate functions are described in <xref
1616 linkend="functions-aggregate">. Other aggregate functions can be added
1621 An aggregate expression can only appear in the result list or
1622 <literal>HAVING</> clause of a <command>SELECT</> command.
1623 It is forbidden in other clauses, such as <literal>WHERE</>,
1624 because those clauses are logically evaluated before the results
1625 of aggregates are formed.
1629 When an aggregate expression appears in a subquery (see
1630 <xref linkend="sql-syntax-scalar-subqueries"> and
1631 <xref linkend="functions-subquery">), the aggregate is normally
1632 evaluated over the rows of the subquery. But an exception occurs
1633 if the aggregate's arguments contain only outer-level variables:
1634 the aggregate then belongs to the nearest such outer level, and is
1635 evaluated over the rows of that query. The aggregate expression
1636 as a whole is then an outer reference for the subquery it appears in,
1637 and acts as a constant over any one evaluation of that subquery.
1638 The restriction about
1639 appearing only in the result list or <literal>HAVING</> clause
1640 applies with respect to the query level that the aggregate belongs to.
1644 <sect2 id="syntax-window-functions">
1645 <title>Window Function Calls</title>
1647 <indexterm zone="syntax-window-functions">
1648 <primary>window function</primary>
1649 <secondary>invocation</secondary>
1652 <indexterm zone="syntax-window-functions">
1653 <primary>OVER clause</primary>
1657 A <firstterm>window function call</firstterm> represents the application
1658 of an aggregate-like function over some portion of the rows selected
1659 by a query. Unlike regular aggregate function calls, this is not tied
1660 to grouping of the selected rows into a single output row — each
1661 row remains separate in the query output. However the window function
1662 is able to scan all the rows that would be part of the current row's
1663 group according to the grouping specification (<literal>PARTITION BY</>
1664 list) of the window function call.
1665 The syntax of a window function call is one of the following:
1668 <replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER ( <replaceable class="parameter">window_definition</replaceable> )
1669 <replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER <replaceable>window_name</replaceable>
1670 <replaceable>function_name</replaceable> ( * ) OVER ( <replaceable class="parameter">window_definition</replaceable> )
1671 <replaceable>function_name</replaceable> ( * ) OVER <replaceable>window_name</replaceable>
1673 where <replaceable class="parameter">window_definition</replaceable>
1676 [ <replaceable class="parameter">existing_window_name</replaceable> ]
1677 [ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
1678 [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
1679 [ <replaceable class="parameter">frame_clause</replaceable> ]
1681 and the optional <replaceable class="parameter">frame_clause</replaceable>
1684 [ RANGE | ROWS ] <replaceable>frame_start</>
1685 [ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</>
1687 where <replaceable>frame_start</> and <replaceable>frame_end</> can be
1691 <replaceable>value</replaceable> PRECEDING
1693 <replaceable>value</replaceable> FOLLOWING
1699 Here, <replaceable>expression</replaceable> represents any value
1700 expression that does not itself contain window function calls.
1701 The <literal>PARTITION BY</> and <literal>ORDER BY</> lists have
1702 essentially the same syntax and semantics as <literal>GROUP BY</>
1703 and <literal>ORDER BY</> clauses of the whole query, except that their
1704 expressions are always just expressions and cannot be output-column
1706 <replaceable>window_name</replaceable> is a reference to a named window
1707 specification defined in the query's <literal>WINDOW</literal> clause.
1708 Named window specifications are usually referenced with just
1709 <literal>OVER</> <replaceable>window_name</replaceable>, but it is
1710 also possible to write a window name inside the parentheses and then
1711 optionally supply an ordering clause and/or frame clause (the referenced
1712 window must lack these clauses, if they are supplied here).
1713 This latter syntax follows the same rules as modifying an existing
1714 window name within the <literal>WINDOW</literal> clause; see the
1715 <xref linkend="sql-select"> reference
1720 The <replaceable class="parameter">frame_clause</replaceable> specifies
1721 the set of rows constituting the <firstterm>window frame</>, for those
1722 window functions that act on the frame instead of the whole partition.
1723 If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT
1724 ROW</>. Restrictions are that
1725 <replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>,
1726 <replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>,
1727 and the <replaceable>frame_end</> choice cannot appear earlier in the
1728 above list than the <replaceable>frame_start</> choice — for example
1729 <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</>
1730 PRECEDING</literal> is not allowed.
1731 The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
1732 which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
1733 CURRENT ROW</>; it sets the frame to be all rows from the partition start
1734 up through the current row's last peer in the <literal>ORDER BY</>
1735 ordering (which means all rows if there is no <literal>ORDER BY</>).
1736 In general, <literal>UNBOUNDED PRECEDING</> means that the frame
1737 starts with the first row of the partition, and similarly
1738 <literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last
1739 row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</>
1740 mode). In <literal>ROWS</> mode, <literal>CURRENT ROW</>
1741 means that the frame starts or ends with the current row; but in
1742 <literal>RANGE</> mode it means that the frame starts or ends with
1743 the current row's first or last peer in the <literal>ORDER BY</> ordering.
1744 The <replaceable>value</> <literal>PRECEDING</> and
1745 <replaceable>value</> <literal>FOLLOWING</> cases are currently only
1746 allowed in <literal>ROWS</> mode. They indicate that the frame starts
1747 or ends with the row that many rows before or after the current row.
1748 <replaceable>value</replaceable> must be an integer expression not
1749 containing any variables, aggregate functions, or window functions.
1750 The value must not be null or negative; but it can be zero, which
1751 selects the current row itself.
1755 The built-in window functions are described in <xref
1756 linkend="functions-window-table">. Other window functions can be added by
1757 the user. Also, any built-in or user-defined aggregate function can be
1758 used as a window function.
1762 The syntaxes using <literal>*</> are used for calling parameter-less
1763 aggregate functions as window functions, for example
1764 <literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
1765 <literal>*</> is customarily not used for non-aggregate window functions.
1766 Aggregate window functions, unlike normal aggregate functions, do not
1767 allow <literal>DISTINCT</> or <literal>ORDER BY</> to be used within the
1768 function argument list.
1772 Window function calls are permitted only in the <literal>SELECT</literal>
1773 list and the <literal>ORDER BY</> clause of the query.
1777 More information about window functions can be found in
1778 <xref linkend="tutorial-window">,
1779 <xref linkend="functions-window">,
1780 <xref linkend="queries-window">.
1784 <sect2 id="sql-syntax-type-casts">
1785 <title>Type Casts</title>
1788 <primary>data type</primary>
1789 <secondary>type cast</secondary>
1793 <primary>type cast</primary>
1797 <primary>::</primary>
1801 A type cast specifies a conversion from one data type to another.
1802 <productname>PostgreSQL</productname> accepts two equivalent syntaxes
1805 CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> )
1806 <replaceable>expression</replaceable>::<replaceable>type</replaceable>
1808 The <literal>CAST</> syntax conforms to SQL; the syntax with
1809 <literal>::</literal> is historical <productname>PostgreSQL</productname>
1814 When a cast is applied to a value expression of a known type, it
1815 represents a run-time type conversion. The cast will succeed only
1816 if a suitable type conversion operation has been defined. Notice that this
1817 is subtly different from the use of casts with constants, as shown in
1818 <xref linkend="sql-syntax-constants-generic">. A cast applied to an
1819 unadorned string literal represents the initial assignment of a type
1820 to a literal constant value, and so it will succeed for any type
1821 (if the contents of the string literal are acceptable input syntax for the
1826 An explicit type cast can usually be omitted if there is no ambiguity as
1827 to the type that a value expression must produce (for example, when it is
1828 assigned to a table column); the system will automatically apply a
1829 type cast in such cases. However, automatic casting is only done for
1830 casts that are marked <quote>OK to apply implicitly</>
1831 in the system catalogs. Other casts must be invoked with
1832 explicit casting syntax. This restriction is intended to prevent
1833 surprising conversions from being applied silently.
1837 It is also possible to specify a type cast using a function-like
1840 <replaceable>typename</replaceable> ( <replaceable>expression</replaceable> )
1842 However, this only works for types whose names are also valid as
1843 function names. For example, <literal>double precision</literal>
1844 cannot be used this way, but the equivalent <literal>float8</literal>
1845 can. Also, the names <literal>interval</>, <literal>time</>, and
1846 <literal>timestamp</> can only be used in this fashion if they are
1847 double-quoted, because of syntactic conflicts. Therefore, the use of
1848 the function-like cast syntax leads to inconsistencies and should
1849 probably be avoided.
1854 The function-like syntax is in fact just a function call. When
1855 one of the two standard cast syntaxes is used to do a run-time
1856 conversion, it will internally invoke a registered function to
1857 perform the conversion. By convention, these conversion functions
1858 have the same name as their output type, and thus the <quote>function-like
1859 syntax</> is nothing more than a direct invocation of the underlying
1860 conversion function. Obviously, this is not something that a portable
1861 application should rely on. For further details see
1862 <xref linkend="sql-createcast">.
1867 <sect2 id="sql-syntax-scalar-subqueries">
1868 <title>Scalar Subqueries</title>
1871 <primary>subquery</primary>
1875 A scalar subquery is an ordinary
1876 <command>SELECT</command> query in parentheses that returns exactly one
1877 row with one column. (See <xref linkend="queries"> for information about writing queries.)
1878 The <command>SELECT</command> query is executed
1879 and the single returned value is used in the surrounding value expression.
1880 It is an error to use a query that
1881 returns more than one row or more than one column as a scalar subquery.
1882 (But if, during a particular execution, the subquery returns no rows,
1883 there is no error; the scalar result is taken to be null.)
1884 The subquery can refer to variables from the surrounding query,
1885 which will act as constants during any one evaluation of the subquery.
1886 See also <xref linkend="functions-subquery"> for other expressions involving subqueries.
1890 For example, the following finds the largest city population in each
1893 SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
1899 <sect2 id="sql-syntax-array-constructors">
1900 <title>Array Constructors</title>
1903 <primary>array</primary>
1904 <secondary>constructor</secondary>
1908 <primary>ARRAY</primary>
1912 An array constructor is an expression that builds an
1913 array value using values for its member elements. A simple array
1915 consists of the key word <literal>ARRAY</literal>, a left square bracket
1916 <literal>[</>, a list of expressions (separated by commas) for the
1917 array element values, and finally a right square bracket <literal>]</>.
1920 SELECT ARRAY[1,2,3+4];
1927 the array element type is the common type of the member expressions,
1928 determined using the same rules as for <literal>UNION</> or
1929 <literal>CASE</> constructs (see <xref linkend="typeconv-union-case">).
1930 You can override this by explicitly casting the array constructor to the
1931 desired type, for example:
1933 SELECT ARRAY[1,2,22.7]::integer[];
1939 This has the same effect as casting each expression to the array
1940 element type individually.
1941 For more on casting, see <xref linkend="sql-syntax-type-casts">.
1945 Multidimensional array values can be built by nesting array
1947 In the inner constructors, the key word <literal>ARRAY</literal> can
1948 be omitted. For example, these produce the same result:
1951 SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
1957 SELECT ARRAY[[1,2],[3,4]];
1964 Since multidimensional arrays must be rectangular, inner constructors
1965 at the same level must produce sub-arrays of identical dimensions.
1966 Any cast applied to the outer <literal>ARRAY</> constructor propagates
1967 automatically to all the inner constructors.
1971 Multidimensional array constructor elements can be anything yielding
1972 an array of the proper kind, not only a sub-<literal>ARRAY</> construct.
1975 CREATE TABLE arr(f1 int[], f2 int[]);
1977 INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);
1979 SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;
1981 ------------------------------------------------
1982 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
1988 You can construct an empty array, but since it's impossible to have an
1989 array with no type, you must explicitly cast your empty array to the
1990 desired type. For example:
1992 SELECT ARRAY[]::integer[];
2001 It is also possible to construct an array from the results of a
2002 subquery. In this form, the array constructor is written with the
2003 key word <literal>ARRAY</literal> followed by a parenthesized (not
2004 bracketed) subquery. For example:
2006 SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
2008 -------------------------------------------------------------
2009 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
2012 The subquery must return a single column. The resulting
2013 one-dimensional array will have an element for each row in the
2014 subquery result, with an element type matching that of the
2015 subquery's output column.
2019 The subscripts of an array value built with <literal>ARRAY</literal>
2020 always begin with one. For more information about arrays, see
2021 <xref linkend="arrays">.
2026 <sect2 id="sql-syntax-row-constructors">
2027 <title>Row Constructors</title>
2030 <primary>composite type</primary>
2031 <secondary>constructor</secondary>
2035 <primary>row type</primary>
2036 <secondary>constructor</secondary>
2040 <primary>ROW</primary>
2044 A row constructor is an expression that builds a row value (also
2045 called a composite value) using values
2046 for its member fields. A row constructor consists of the key word
2047 <literal>ROW</literal>, a left parenthesis, zero or more
2048 expressions (separated by commas) for the row field values, and finally
2049 a right parenthesis. For example:
2051 SELECT ROW(1,2.5,'this is a test');
2053 The key word <literal>ROW</> is optional when there is more than one
2054 expression in the list.
2058 A row constructor can include the syntax
2059 <replaceable>rowvalue</replaceable><literal>.*</literal>,
2060 which will be expanded to a list of the elements of the row value,
2061 just as occurs when the <literal>.*</> syntax is used at the top level
2062 of a <command>SELECT</> list. For example, if table <literal>t</> has
2063 columns <literal>f1</> and <literal>f2</>, these are the same:
2065 SELECT ROW(t.*, 42) FROM t;
2066 SELECT ROW(t.f1, t.f2, 42) FROM t;
2072 Before <productname>PostgreSQL</productname> 8.2, the
2073 <literal>.*</literal> syntax was not expanded, so that writing
2074 <literal>ROW(t.*, 42)</> created a two-field row whose first field
2075 was another row value. The new behavior is usually more useful.
2076 If you need the old behavior of nested row values, write the inner
2077 row value without <literal>.*</literal>, for instance
2078 <literal>ROW(t, 42)</>.
2083 By default, the value created by a <literal>ROW</> expression is of
2084 an anonymous record type. If necessary, it can be cast to a named
2085 composite type — either the row type of a table, or a composite type
2086 created with <command>CREATE TYPE AS</>. An explicit cast might be needed
2087 to avoid ambiguity. For example:
2089 CREATE TABLE mytable(f1 int, f2 float, f3 text);
2091 CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;
2093 -- No cast needed since only one getf1() exists
2094 SELECT getf1(ROW(1,2.5,'this is a test'));
2100 CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
2102 CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;
2104 -- Now we need a cast to indicate which function to call:
2105 SELECT getf1(ROW(1,2.5,'this is a test'));
2106 ERROR: function getf1(record) is not unique
2108 SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
2114 SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
2123 Row constructors can be used to build composite values to be stored
2124 in a composite-type table column, or to be passed to a function that
2125 accepts a composite parameter. Also,
2126 it is possible to compare two row values or test a row with
2127 <literal>IS NULL</> or <literal>IS NOT NULL</>, for example:
2129 SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');
2131 SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows
2133 For more detail see <xref linkend="functions-comparisons">.
2134 Row constructors can also be used in connection with subqueries,
2135 as discussed in <xref linkend="functions-subquery">.
2140 <sect2 id="syntax-express-eval">
2141 <title>Expression Evaluation Rules</title>
2144 <primary>expression</primary>
2145 <secondary>order of evaluation</secondary>
2149 The order of evaluation of subexpressions is not defined. In
2150 particular, the inputs of an operator or function are not necessarily
2151 evaluated left-to-right or in any other fixed order.
2155 Furthermore, if the result of an expression can be determined by
2156 evaluating only some parts of it, then other subexpressions
2157 might not be evaluated at all. For instance, if one wrote:
2159 SELECT true OR somefunc();
2161 then <literal>somefunc()</literal> would (probably) not be called
2162 at all. The same would be the case if one wrote:
2164 SELECT somefunc() OR true;
2166 Note that this is not the same as the left-to-right
2167 <quote>short-circuiting</quote> of Boolean operators that is found
2168 in some programming languages.
2172 As a consequence, it is unwise to use functions with side effects
2173 as part of complex expressions. It is particularly dangerous to
2174 rely on side effects or evaluation order in <literal>WHERE</> and <literal>HAVING</> clauses,
2175 since those clauses are extensively reprocessed as part of
2176 developing an execution plan. Boolean
2177 expressions (<literal>AND</>/<literal>OR</>/<literal>NOT</> combinations) in those clauses can be reorganized
2178 in any manner allowed by the laws of Boolean algebra.
2182 When it is essential to force evaluation order, a <literal>CASE</>
2183 construct (see <xref linkend="functions-conditional">) can be
2184 used. For example, this is an untrustworthy way of trying to
2185 avoid division by zero in a <literal>WHERE</> clause:
2187 SELECT ... WHERE x > 0 AND y/x > 1.5;
2191 SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
2193 A <literal>CASE</> construct used in this fashion will defeat optimization
2194 attempts, so it should only be done when necessary. (In this particular
2195 example, it would be better to sidestep the problem by writing
2196 <literal>y > 1.5*x</> instead.)
2201 <sect1 id="sql-syntax-calling-funcs">
2202 <title>Calling Functions</title>
2204 <indexterm zone="sql-syntax-calling-funcs">
2205 <primary>notation</primary>
2206 <secondary>functions</secondary>
2210 <productname>PostgreSQL</productname> allows functions that have named
2211 parameters to be called using either <firstterm>positional</firstterm> or
2212 <firstterm>named</firstterm> notation. Named notation is especially
2213 useful for functions that have a large number of parameters, since it
2214 makes the associations between parameters and actual arguments more
2215 explicit and reliable.
2216 In positional notation, a function call is written with
2217 its argument values in the same order as they are defined in the function
2218 declaration. In named notation, the arguments are matched to the
2219 function parameters by name and can be written in any order.
2223 In either notation, parameters that have default values given in the
2224 function declaration need not be written in the call at all. But this
2225 is particularly useful in named notation, since any combination of
2226 parameters can be omitted; while in positional notation parameters can
2227 only be omitted from right to left.
2231 <productname>PostgreSQL</productname> also supports
2232 <firstterm>mixed</firstterm> notation, which combines positional and
2233 named notation. In this case, positional parameters are written first
2234 and named parameters appear after them.
2238 The following examples will illustrate the usage of all three
2239 notations, using the following function definition:
2241 CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
2246 WHEN $3 THEN UPPER($1 || ' ' || $2)
2247 ELSE LOWER($1 || ' ' || $2)
2250 LANGUAGE SQL IMMUTABLE STRICT;
2252 Function <function>concat_lower_or_upper</function> has two mandatory
2253 parameters, <literal>a</literal> and <literal>b</literal>. Additionally
2254 there is one optional parameter <literal>uppercase</literal> which defaults
2255 to <literal>false</literal>. The <literal>a</literal> and
2256 <literal>b</literal> inputs will be concatenated, and forced to either
2257 upper or lower case depending on the <literal>uppercase</literal>
2258 parameter. The remaining details of this function
2259 definition are not important here (see <xref linkend="extend"> for
2263 <sect2 id="sql-syntax-calling-funcs-positional">
2264 <title>Using positional notation</title>
2267 <primary>function</primary>
2268 <secondary>positional notation</secondary>
2272 Positional notation is the traditional mechanism for passing arguments
2273 to functions in <productname>PostgreSQL</productname>. An example is:
2275 SELECT concat_lower_or_upper('Hello', 'World', true);
2276 concat_lower_or_upper
2277 -----------------------
2281 All arguments are specified in order. The result is upper case since
2282 <literal>uppercase</literal> is specified as <literal>true</literal>.
2285 SELECT concat_lower_or_upper('Hello', 'World');
2286 concat_lower_or_upper
2287 -----------------------
2291 Here, the <literal>uppercase</literal> parameter is omitted, so it
2292 receives its default value of <literal>false</literal>, resulting in
2293 lower case output. In positional notation, arguments can be omitted
2294 from right to left so long as they have defaults.
2298 <sect2 id="sql-syntax-calling-funcs-named">
2299 <title>Using named notation</title>
2302 <primary>function</primary>
2303 <secondary>named notation</secondary>
2307 In named notation, each argument's name is specified using
2308 <literal>:=</literal> to separate it from the argument expression.
2311 SELECT concat_lower_or_upper(a := 'Hello', b := 'World');
2312 concat_lower_or_upper
2313 -----------------------
2317 Again, the argument <literal>uppercase</literal> was omitted
2318 so it is set to <literal>false</literal> implicitly. One advantage of
2319 using named notation is that the arguments may be specified in any
2322 SELECT concat_lower_or_upper(a := 'Hello', b := 'World', uppercase := true);
2323 concat_lower_or_upper
2324 -----------------------
2328 SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World');
2329 concat_lower_or_upper
2330 -----------------------
2337 <sect2 id="sql-syntax-calling-funcs-mixed">
2338 <title>Using mixed notation</title>
2341 <primary>function</primary>
2342 <secondary>mixed notation</secondary>
2346 The mixed notation combines positional and named notation. However, as
2347 already mentioned, named arguments cannot precede positional arguments.
2350 SELECT concat_lower_or_upper('Hello', 'World', uppercase := true);
2351 concat_lower_or_upper
2352 -----------------------
2356 In the above query, the arguments <literal>a</literal> and
2357 <literal>b</literal> are specified positionally, while
2358 <literal>uppercase</> is specified by name. In this example,
2359 that adds little except documentation. With a more complex function
2360 having numerous parameters that have default values, named or mixed
2361 notation can save a great deal of writing and reduce chances for error.