2 $Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.65 2002/08/10 19:01:53 tgl Exp $
5 <chapter id="sql-syntax">
6 <title>SQL Syntax</title>
8 <indexterm zone="sql-syntax">
9 <primary>syntax</primary>
10 <secondary>SQL</secondary>
14 This chapter describes the syntax of SQL. It forms the foundation
15 for understanding the following chapters which will go into detail
16 about how the SQL commands are applied to define and modify data.
20 We also advise users who are already familiar with SQL to read this
21 chapter carefully because there are several rules and concepts that
22 are implemented inconsistently among SQL databases or that are
23 specific to <productname>PostgreSQL</productname>.
26 <sect1 id="sql-syntax-lexical">
27 <title>Lexical Structure</title>
30 SQL input consists of a sequence of
31 <firstterm>commands</firstterm>. A command is composed of a
32 sequence of <firstterm>tokens</firstterm>, terminated by a
33 semicolon (<quote>;</quote>). The end of the input stream also
34 terminates a command. Which tokens are valid depends on the syntax
35 of the particular command.
39 A token can be a <firstterm>key word</firstterm>, an
40 <firstterm>identifier</firstterm>, a <firstterm>quoted
41 identifier</firstterm>, a <firstterm>literal</firstterm> (or
42 constant), or a special character symbol. Tokens are normally
43 separated by whitespace (space, tab, newline), but need not be if
44 there is no ambiguity (which is generally only the case if a
45 special character is adjacent to some other token type).
49 Additionally, <firstterm>comments</firstterm> can occur in SQL
50 input. They are not tokens, they are effectively equivalent to
55 For example, the following is (syntactically) valid SQL input:
57 SELECT * FROM MY_TABLE;
58 UPDATE MY_TABLE SET A = 5;
59 INSERT INTO MY_TABLE VALUES (3, 'hi there');
61 This is a sequence of three commands, one per line (although this
62 is not required; more than one command can be on a line, and
63 commands can usefully be split across lines).
67 The SQL syntax is not very consistent regarding what tokens
68 identify commands and which are operands or parameters. The first
69 few tokens are generally the command name, so in the above example
70 we would usually speak of a <quote>SELECT</quote>, an
71 <quote>UPDATE</quote>, and an <quote>INSERT</quote> command. But
72 for instance the <command>UPDATE</command> command always requires
73 a <token>SET</token> token to appear in a certain position, and
74 this particular variation of <command>INSERT</command> also
75 requires a <token>VALUES</token> in order to be complete. The
76 precise syntax rules for each command are described in the
77 <citetitle>Reference Manual</citetitle>.
80 <sect2 id="sql-syntax-identifiers">
81 <title>Identifiers and Key Words</title>
83 <indexterm zone="sql-syntax-identifiers">
84 <primary>identifiers</primary>
87 <indexterm zone="sql-syntax-identifiers">
88 <primary>key words</primary>
89 <secondary>syntax</secondary>
93 Tokens such as <token>SELECT</token>, <token>UPDATE</token>, or
94 <token>VALUES</token> in the example above are examples of
95 <firstterm>key words</firstterm>, that is, words that have a fixed
96 meaning in the SQL language. The tokens <token>MY_TABLE</token>
97 and <token>A</token> are examples of
98 <firstterm>identifiers</firstterm>. They identify names of
99 tables, columns, or other database objects, depending on the
100 command they are used in. Therefore they are sometimes simply
101 called <quote>names</quote>. Key words and identifiers have the
102 same lexical structure, meaning that one cannot know whether a
103 token is an identifier or a key word without knowing the language.
104 A complete list of key words can be found in <xref
105 linkend="sql-keywords-appendix">.
109 SQL identifiers and key words must begin with a letter
110 (<literal>a</literal>-<literal>z</literal>, but also letters with
111 diacritical marks and non-Latin letters) or an underscore
112 (<literal>_</literal>). Subsequent characters in an identifier or
113 key word can be letters, digits
114 (<literal>0</literal>-<literal>9</literal>), or underscores,
115 although the SQL standard will not define a key word that contains
116 digits or starts or ends with an underscore.
120 The system uses no more than <symbol>NAMEDATALEN</symbol>-1
121 characters of an identifier; longer names can be written in
122 commands, but they will be truncated. By default,
123 <symbol>NAMEDATALEN</symbol> is 32 so the maximum identifier length
124 is 31 (but at the time the system is built,
125 <symbol>NAMEDATALEN</symbol> can be changed in
126 <filename>src/include/postgres_ext.h</filename>).
131 <primary>case sensitivity</primary>
132 <secondary>SQL commands</secondary>
134 Identifier and key word names are case insensitive. Therefore
136 UPDATE MY_TABLE SET A = 5;
138 can equivalently be written as
140 uPDaTE my_TabLE SeT a = 5;
142 A convention often used is to write key words in upper
143 case and names in lower case, e.g.,
145 UPDATE my_table SET a = 5;
151 <primary>quotes</primary>
152 <secondary>and identifiers</secondary>
154 There is a second kind of identifier: the <firstterm>delimited
155 identifier</firstterm> or <firstterm>quoted
156 identifier</firstterm>. It is formed by enclosing an arbitrary
157 sequence of characters in double-quotes
158 (<literal>"</literal>). <!-- " font-lock mania --> A delimited
159 identifier is always an identifier, never a key word. So
160 <literal>"select"</literal> could be used to refer to a column or
161 table named <quote>select</quote>, whereas an unquoted
162 <literal>select</literal> would be taken as a key word and
163 would therefore provoke a parse error when used where a table or
164 column name is expected. The example can be written with quoted
165 identifiers like this:
167 UPDATE "my_table" SET "a" = 5;
172 Quoted identifiers can contain any character other than a double
173 quote itself. This allows constructing table or column names that
174 would otherwise not be possible, such as ones containing spaces or
175 ampersands. The length limitation still applies.
179 Quoting an identifier also makes it case-sensitive, whereas
180 unquoted names are always folded to lower case. For example, the
181 identifiers <literal>FOO</literal>, <literal>foo</literal> and
182 <literal>"foo"</literal> are considered the same by
183 <productname>PostgreSQL</productname>, but <literal>"Foo"</literal>
184 and <literal>"FOO"</literal> are different from these three and
188 The folding of unquoted names to lower case in <productname>PostgreSQL</>
189 is incompatible with the SQL standard, which says that unquoted
190 names should be folded to upper case. Thus, <literal>foo</literal>
191 should be equivalent to <literal>"FOO"</literal> not
192 <literal>"foo"</literal> according to the standard. If you want to
193 write portable applications you are advised to always quote a particular
194 name or never quote it.
201 <sect2 id="sql-syntax-constants">
202 <title>Constants</title>
204 <indexterm zone="sql-syntax-constants">
205 <primary>constants</primary>
209 There are four kinds of <firstterm>implicitly-typed
210 constants</firstterm> in <productname>PostgreSQL</productname>:
211 strings, bit strings, integers, and floating-point numbers.
212 Constants can also be specified with explicit types, which can
213 enable more accurate representation and more efficient handling by
214 the system. The implicit constants are described below; explicit
215 constants are discussed afterwards.
218 <sect3 id="sql-syntax-strings">
219 <title>String Constants</title>
221 <indexterm zone="sql-syntax-strings">
222 <primary>character strings</primary>
223 <secondary>constants</secondary>
228 <primary>quotes</primary>
229 <secondary>escaping</secondary>
231 A string constant in SQL is an arbitrary sequence of characters
232 bounded by single quotes (<quote>'</quote>), e.g., <literal>'This
233 is a string'</literal>. SQL allows single quotes to be embedded
234 in strings by typing two adjacent single quotes (e.g.,
235 <literal>'Dianne''s horse'</literal>). In
236 <productname>PostgreSQL</productname> single quotes may
237 alternatively be escaped with a backslash (<quote>\</quote>,
238 e.g., <literal>'Dianne\'s horse'</literal>).
242 C-style backslash escapes are also available:
243 <literal>\b</literal> is a backspace, <literal>\f</literal> is a
244 form feed, <literal>\n</literal> is a newline,
245 <literal>\r</literal> is a carriage return, <literal>\t</literal>
246 is a tab, and <literal>\<replaceable>xxx</replaceable></literal>,
247 where <replaceable>xxx</replaceable> is an octal number, is the
248 character with the corresponding ASCII code. Any other character
249 following a backslash is taken literally. Thus, to include a
250 backslash in a string constant, type two backslashes.
254 The character with the code zero cannot be in a string constant.
258 Two string constants that are only separated by whitespace
259 <emphasis>with at least one newline</emphasis> are concatenated
260 and effectively treated as if the string had been written in one
261 constant. For example:
274 is not valid syntax, and <productname>PostgreSQL</productname> is
275 consistent with <acronym>SQL9x</acronym> in this regard.
279 <sect3 id="sql-syntax-bit-strings">
280 <title>Bit-String Constants</title>
282 <indexterm zone="sql-syntax-bit-strings">
283 <primary>bit strings</primary>
284 <secondary>constants</secondary>
288 Bit-string constants look like string constants with a
289 <literal>B</literal> (upper or lower case) immediately before the
290 opening quote (no intervening whitespace), e.g.,
291 <literal>B'1001'</literal>. The only characters allowed within
292 bit-string constants are <literal>0</literal> and
293 <literal>1</literal>. Bit-string constants can be continued
294 across lines in the same way as regular string constants.
299 <title>Integer Constants</title>
302 Integer constants in SQL are sequences of decimal digits (0
303 though 9) with no decimal point and no exponent. The range of legal values
304 depends on which integer data type is used, but the plain
305 <type>integer</type> type accepts values ranging from -2147483648
306 to +2147483647. (The optional plus or minus sign is actually a
307 separate unary operator and not part of the integer constant.)
312 <title>Floating-Point Constants</title>
315 <primary>floating point</primary>
316 <secondary>constants</secondary>
320 Floating-point constants are accepted in these general forms:
322 <replaceable>digits</replaceable>.<optional><replaceable>digits</replaceable></optional><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
323 <optional><replaceable>digits</replaceable></optional>.<replaceable>digits</replaceable><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
324 <replaceable>digits</replaceable>e<optional>+-</optional><replaceable>digits</replaceable>
326 where <replaceable>digits</replaceable> is one or more decimal
327 digits. At least one digit must be before or after the decimal
328 point. At least one digit must follow the exponent delimiter
329 (<literal>e</literal>) if that field is present.
330 Thus, a floating-point constant is distinguished from an integer
331 constant by the presence of either the decimal point or the
332 exponent clause (or both). There must not be a space or other
333 characters embedded in the constant.
337 These are some examples of valid floating-point constants:
348 Floating-point constants are of type <type>DOUBLE
349 PRECISION</type>. <type>REAL</type> can be specified explicitly
350 by using <acronym>SQL</acronym> string notation or
351 <productname>PostgreSQL</productname> type notation:
354 REAL '1.23' -- string style
355 '1.23'::REAL -- PostgreSQL (historical) style
360 <sect3 id="sql-syntax-constants-generic">
361 <title>Constants of Other Types</title>
364 <primary>data types</primary>
365 <secondary>constants</secondary>
369 A constant of an <emphasis>arbitrary</emphasis> type can be
370 entered using any one of the following notations:
372 <replaceable>type</replaceable> '<replaceable>string</replaceable>'
373 '<replaceable>string</replaceable>'::<replaceable>type</replaceable>
374 CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
376 The string's text is passed to the input conversion
377 routine for the type called <replaceable>type</replaceable>. The
378 result is a constant of the indicated type. The explicit type
379 cast may be omitted if there is no ambiguity as to the type the
380 constant must be (for example, when it is passed as an argument
381 to a non-overloaded function), in which case it is automatically
386 It is also possible to specify a type coercion using a function-like
389 <replaceable>typename</replaceable> ( '<replaceable>string</replaceable>' )
391 but not all type names may be used in this way; see <xref
392 linkend="sql-syntax-type-casts"> for details.
396 The <literal>::</literal>, <literal>CAST()</literal>, and
397 function-call syntaxes can also be used to specify run-time type
398 conversions of arbitrary expressions, as discussed in <xref
399 linkend="sql-syntax-type-casts">. But the form
400 <replaceable>type</replaceable> '<replaceable>string</replaceable>'
401 can only be used to specify the type of a literal constant.
402 Another restriction on
403 <replaceable>type</replaceable> '<replaceable>string</replaceable>'
404 is that it does not work for array types; use <literal>::</literal>
405 or <literal>CAST()</literal> to specify the type of an array constant.
410 <title>Array constants</title>
413 <primary>arrays</primary>
414 <secondary>constants</secondary>
418 The general format of an array constant is the following:
420 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
422 where <replaceable>delim</replaceable> is the delimiter character
423 for the type, as recorded in its <literal>pg_type</literal>
424 entry. (For all built-in types, this is the comma character
425 <quote><literal>,</literal></>.) Each <replaceable>val</replaceable> is either a constant
426 of the array element type, or a subarray. An example of an
429 '{{1,2,3},{4,5,6},{7,8,9}}'
431 This constant is a two-dimensional, 3-by-3 array consisting of three
432 subarrays of integers.
436 Individual array elements can be placed between double-quote
437 marks (<literal>"</literal>) <!-- " --> to avoid ambiguity
438 problems with respect to whitespace. Without quote marks, the
439 array-value parser will skip leading whitespace.
443 (Array constants are actually only a special case of the generic
444 type constants discussed in the previous section. The constant
445 is initially treated as a string and passed to the array input
446 conversion routine. An explicit type specification might be
453 <sect2 id="sql-syntax-operators">
454 <title>Operators</title>
456 <indexterm zone="sql-syntax-operators">
457 <primary>operators</primary>
458 <secondary>syntax</secondary>
462 An operator is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
463 (31 by default) characters from the following list:
465 + - * / < > = ~ ! @ # % ^ & | ` ? $
468 There are a few restrictions on operator names, however:
472 <literal>$</> (dollar) cannot be a single-character operator, although it
473 can be part of a multiple-character operator name.
479 <literal>--</literal> and <literal>/*</literal> cannot appear
480 anywhere in an operator name, since they will be taken as the
487 A multiple-character operator name cannot end in <literal>+</> or <literal>-</>,
488 unless the name also contains at least one of these characters:
490 ~ ! @ # % ^ & | ` ? $
492 For example, <literal>@-</literal> is an allowed operator name,
493 but <literal>*-</literal> is not. This restriction allows
494 <productname>PostgreSQL</productname> to parse SQL-compliant
495 queries without requiring spaces between tokens.
502 When working with non-SQL-standard operator names, you will usually
503 need to separate adjacent operators with spaces to avoid ambiguity.
504 For example, if you have defined a left unary operator named <literal>@</literal>,
505 you cannot write <literal>X*@Y</literal>; you must write
506 <literal>X* @Y</literal> to ensure that
507 <productname>PostgreSQL</productname> reads it as two operator names
513 <title>Special Characters</title>
516 Some characters that are not alphanumeric have a special meaning
517 that is different from being an operator. Details on the usage can
518 be found at the location where the respective syntax element is
519 described. This section only exists to advise the existence and
520 summarize the purposes of these characters.
525 A dollar sign (<literal>$</literal>) followed by digits is used
526 to represent the positional parameters in the body of a function
527 definition. In other contexts the dollar sign may be part of an
534 Parentheses (<literal>()</literal>) have their usual meaning to
535 group expressions and enforce precedence. In some cases
536 parentheses are required as part of the fixed syntax of a
537 particular SQL command.
543 Brackets (<literal>[]</literal>) are used to select the elements
544 of an array. See <xref linkend="arrays"> for more information
551 Commas (<literal>,</literal>) are used in some syntactical
552 constructs to separate the elements of a list.
558 The semicolon (<literal>;</literal>) terminates an SQL command.
559 It cannot appear anywhere within a command, except within a
560 string constant or quoted identifier.
566 The colon (<literal>:</literal>) is used to select
567 <quote>slices</quote> from arrays. (See <xref
568 linkend="arrays">.) In certain SQL dialects (such as Embedded
569 SQL), the colon is used to prefix variable names.
575 The asterisk (<literal>*</literal>) has a special meaning when
576 used in the <command>SELECT</command> command or with the
577 <function>COUNT</function> aggregate function.
583 The period (<literal>.</literal>) is used in floating-point
584 constants, and to separate table and column names.
592 <sect2 id="sql-syntax-comments">
593 <title>Comments</title>
595 <indexterm zone="sql-syntax-comments">
596 <primary>comments</primary>
597 <secondary>in SQL</secondary>
601 A comment is an arbitrary sequence of characters beginning with
602 double dashes and extending to the end of the line, e.g.:
604 -- This is a standard SQL92 comment
609 Alternatively, C-style block comments can be used:
612 * with nesting: /* nested block comment */
615 where the comment begins with <literal>/*</literal> and extends to
616 the matching occurrence of <literal>*/</literal>. These block
617 comments nest, as specified in SQL99 but unlike C, so that one can
618 comment out larger blocks of code that may contain existing block
623 A comment is removed from the input stream before further syntax
624 analysis and is effectively replaced by whitespace.
628 <sect2 id="sql-precedence">
629 <title>Lexical Precedence</title>
631 <indexterm zone="sql-precedence">
632 <primary>operators</primary>
633 <secondary>precedence</secondary>
637 The precedence and associativity of the operators is hard-wired
638 into the parser. Most operators have the same precedence and are
639 left-associative. This may lead to non-intuitive behavior; for
640 example the Boolean operators <literal><</> and <literal>></> have a different
641 precedence than the Boolean operators <literal><=</> and <literal>>=</>. Also,
642 you will sometimes need to add parentheses when using combinations
643 of binary and unary operators. For instance
651 because the parser has no idea -- until it is too late -- that
652 <token>!</token> is defined as a postfix operator, not an infix one.
653 To get the desired behavior in this case, you must write
657 This is the price one pays for extensibility.
661 <title>Operator Precedence (decreasing)</title>
666 <entry>Operator/Element</entry>
667 <entry>Associativity</entry>
668 <entry>Description</entry>
674 <entry><token>.</token></entry>
676 <entry>table/column name separator</entry>
680 <entry><token>::</token></entry>
682 <entry><productname>PostgreSQL</productname>-style typecast</entry>
686 <entry><token>[</token> <token>]</token></entry>
688 <entry>array element selection</entry>
692 <entry><token>-</token></entry>
694 <entry>unary minus</entry>
698 <entry><token>^</token></entry>
700 <entry>exponentiation</entry>
704 <entry><token>*</token> <token>/</token> <token>%</token></entry>
706 <entry>multiplication, division, modulo</entry>
710 <entry><token>+</token> <token>-</token></entry>
712 <entry>addition, subtraction</entry>
716 <entry><token>IS</token></entry>
718 <entry>test for TRUE, FALSE, UNKNOWN, NULL</entry>
722 <entry><token>ISNULL</token></entry>
724 <entry>test for NULL</entry>
728 <entry><token>NOTNULL</token></entry>
730 <entry>test for NOT NULL</entry>
734 <entry>(any other)</entry>
736 <entry>all other native and user-defined operators</entry>
740 <entry><token>IN</token></entry>
742 <entry>set membership</entry>
746 <entry><token>BETWEEN</token></entry>
748 <entry>containment</entry>
752 <entry><token>OVERLAPS</token></entry>
754 <entry>time interval overlap</entry>
758 <entry><token>LIKE</token> <token>ILIKE</token> <token>SIMILAR</token></entry>
760 <entry>string pattern matching</entry>
764 <entry><token><</token> <token>></token></entry>
766 <entry>less than, greater than</entry>
770 <entry><token>=</token></entry>
772 <entry>equality, assignment</entry>
776 <entry><token>NOT</token></entry>
778 <entry>logical negation</entry>
782 <entry><token>AND</token></entry>
784 <entry>logical conjunction</entry>
788 <entry><token>OR</token></entry>
790 <entry>logical disjunction</entry>
797 Note that the operator precedence rules also apply to user-defined
798 operators that have the same names as the built-in operators
799 mentioned above. For example, if you define a
800 <quote>+</quote> operator for some custom data type it will have
801 the same precedence as the built-in <quote>+</quote> operator, no
802 matter what yours does.
806 When a schema-qualified operator name is used in the
807 <literal>OPERATOR</> syntax, as for example in
809 SELECT 3 OPERATOR(pg_catalog.+) 4;
811 the <literal>OPERATOR</> construct is taken to have the default precedence
812 shown above for <quote>any other</> operator. This is true no matter
813 which specific operator name appears inside <literal>OPERATOR()</>.
818 <sect1 id="sql-naming">
819 <title>Schemas and Naming Conventions</title>
822 <primary>schemas</primary>
826 <primary>search path</primary>
830 <primary>namespaces</primary>
834 A <productname>PostgreSQL</productname> database cluster (installation)
835 contains one or more named databases. Users and groups of users are
836 shared across the entire cluster, but no other data is shared across
837 databases. Any given client connection to the server can access
838 only the data in a single database, the one specified in the connection
844 Users of a cluster do not necessarily have the privilege to access every
845 database in the cluster. Sharing of user names means that there
846 cannot be different users named, say, <literal>joe</> in two databases
847 in the same cluster; but the system can be configured to allow
848 <literal>joe</> access to only some of the databases.
853 <title>Schema Object Names</title>
856 A database contains one or more named <firstterm>schemas</>, which
857 in turn contain tables. Schemas also contain other kinds of named
858 objects, including datatypes, functions, and operators. The same
859 object name can be used in different schemas without conflict; for
860 example, both <literal>schema1</> and <literal>myschema</> may
861 contain tables named <literal>mytable</>. Unlike databases, schemas
862 are not rigidly separated: a user may access objects in any of the
863 schemas in the database he is connected to, if he has privileges
868 <primary>qualified names</primary>
872 <primary>names</primary>
873 <secondary>qualified</secondary>
877 To name a table precisely, write a <firstterm>qualified name</> consisting
878 of the schema name and table name separated by a dot:
880 <replaceable>schema</><literal>.</><replaceable>table</>
882 Actually, the even more general syntax
884 <replaceable>database</><literal>.</><replaceable>schema</><literal>.</><replaceable>table</>
886 can be used too, but at present this is just for pro-forma compliance
887 with the SQL standard; if you write a database name it must be the
888 same as the database you are connected to.
892 <primary>unqualified names</primary>
896 <primary>names</primary>
897 <secondary>unqualified</secondary>
901 Qualified names are tedious to write, and it's often best not to
902 wire a particular schema name into applications anyway. Therefore
903 tables are often referred to by <firstterm>unqualified names</>,
904 which consist of just the table name. The system determines which table
905 is meant by following a <firstterm>search path</>, which is a list
906 of schemas to look in. The first matching table in the search path
907 is taken to be the one wanted. If there is no match in the search
908 path, an error is reported, even if matching table names exist
909 in other schemas in the database.
913 The first schema named in the search path is called the current schema.
914 Aside from being the first schema searched, it is also the schema in
915 which new tables will be created if the <command>CREATE TABLE</>
916 command does not specify a schema name.
920 The search path works in the same way for datatype names, function names,
921 and operator names as it does for table names. Datatype and function
922 names can be qualified in exactly the same way as table names. If you
923 need to write a qualified operator name in an expression, there is a
924 special provision: you must write
926 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operator</><literal>)</>
928 This is needed to avoid syntactic ambiguity. An example is
930 SELECT 3 OPERATOR(pg_catalog.+) 4;
932 In practice one usually relies on the search path for operators,
933 so as not to have to write anything so ugly as that.
937 The standard search path in <productname>PostgreSQL</productname>
938 contains first the schema having the same name as the session user
939 (if it exists), and second the schema named <literal>public</>
940 (if it exists, which it does by default). This arrangement allows
941 a flexible combination of private and shared tables. If no per-user
942 schemas are created then all user tables will exist in the shared
943 <literal>public</> schema, providing behavior that is backwards-compatible
944 with pre-7.3 <productname>PostgreSQL</productname> releases.
949 There is no concept of a <literal>public</> schema in the SQL standard.
950 To achieve closest conformance to the standard, the DBA should
951 create per-user schemas for every user, and not use (perhaps even
952 remove) the <literal>public</> schema.
957 In addition to <literal>public</> and user-created schemas, each database
959 <literal>pg_catalog</> schema, which contains the system tables
960 and all the built-in datatypes, functions, and operators.
961 <literal>pg_catalog</> is always effectively part of the search path.
962 If it is not named explicitly in the path then it is implicitly searched
963 <emphasis>before</> searching the path's schemas. This ensures that
964 built-in names will always be findable. However, you may explicitly
965 place <literal>pg_catalog</> at the end of your search path if you
966 prefer to have user-defined names override built-in names.
970 <sect2 id="sql-reserved-names">
971 <title>Reserved names</title>
974 <primary>reserved names</primary>
978 <primary>names</primary>
979 <secondary>reserved</secondary>
983 There are several restrictions on the names that can be chosen for
984 user-defined database objects. These restrictions vary depending
985 on the kind of object. (Note that these restrictions are
986 separate from whether the name is a key word or not; quoting a
987 name will not allow you to escape these restrictions.)
991 Schema names beginning with <literal>pg_</> are reserved for system
992 purposes and may not be created by users.
996 In <productname>PostgreSQL</productname> versions before 7.3, table
997 names beginning with <literal>pg_</> were reserved. This is no longer
998 true: you may create such a table name if you wish, in any non-system
999 schema. However, it's best to continue to avoid such names,
1000 to ensure that you won't suffer a conflict if some future version
1001 defines a system catalog named the same as your table. (With the
1002 default search path, an unqualified reference to your table name
1003 would be resolved as the system catalog instead.) System catalogs will
1004 continue to follow the convention of having names beginning with
1005 <literal>pg_</>, so that they will not conflict with unqualified
1006 user-table names so long as users avoid the <literal>pg_</> prefix.
1010 Every table has several <firstterm>system columns</> that are
1011 implicitly defined by the system. Therefore, these names cannot
1012 be used as names of user-defined columns:
1015 <primary>columns</primary>
1016 <secondary>system columns</secondary>
1021 <term><structfield>oid</></term>
1025 <primary>OID</primary>
1027 The object identifier (object ID) of a row. This is a serial number
1028 that is automatically added by <productname>PostgreSQL</productname> to all table rows (unless
1029 the table was created WITHOUT OIDS, in which case this column is
1030 not present). See <xref linkend="datatype-oid"> for more info.
1036 <term><structfield>tableoid</></term>
1039 The OID of the table containing this row. This attribute is
1040 particularly handy for queries that select from inheritance
1041 hierarchies, since without it, it's difficult to tell which
1042 individual table a row came from. The
1043 <structfield>tableoid</structfield> can be joined against the
1044 <structfield>oid</structfield> column of
1045 <classname>pg_class</classname> to obtain the table name.
1051 <term><structfield>xmin</></term>
1054 The identity (transaction ID) of the inserting transaction for
1055 this tuple. (Note: A tuple is an individual state of a row;
1056 each update of a row creates a new tuple for the same logical row.)
1062 <term><structfield>cmin</></term>
1065 The command identifier (starting at zero) within the inserting
1072 <term><structfield>xmax</></term>
1075 The identity (transaction ID) of the deleting transaction,
1076 or zero for an undeleted tuple. It is possible for this field
1077 to be nonzero in a visible tuple: that usually indicates that the
1078 deleting transaction hasn't committed yet, or that an attempted
1079 deletion was rolled back.
1085 <term><structfield>cmax</></term>
1088 The command identifier within the deleting transaction, or zero.
1094 <term><structfield>ctid</></term>
1097 The physical location of the tuple within its table.
1098 Note that although the <structfield>ctid</structfield>
1099 can be used to locate the tuple very quickly, a row's
1100 <structfield>ctid</structfield> will change each time it is updated
1101 or moved by <command>VACUUM FULL</>.
1102 Therefore <structfield>ctid</structfield> is useless as a long-term
1104 The OID, or even better a user-defined serial number, should
1105 be used to identify logical rows.
1114 <sect1 id="sql-expressions">
1115 <title>Value Expressions</title>
1118 Value expressions are used in a variety of contexts, such
1119 as in the target list of the <command>SELECT</command> command, as
1120 new column values in <command>INSERT</command> or
1121 <command>UPDATE</command>, or in search conditions in a number of
1122 commands. The result of a value expression is sometimes called a
1123 <firstterm>scalar</firstterm>, to distinguish it from the result of
1124 a table expression (which is a table). Value expressions are
1125 therefore also called <firstterm>scalar expressions</firstterm> (or
1126 even simply <firstterm>expressions</firstterm>). The expression
1127 syntax allows the calculation of values from primitive parts using
1128 arithmetic, logical, set, and other operations.
1132 A value expression is one of the following:
1137 A constant or literal value; see <xref linkend="sql-syntax-constants">.
1149 A positional parameter reference, in the body of a function declaration.
1155 An operator invocation.
1167 An aggregate expression.
1184 <synopsis>( <replaceable>expression</replaceable> )</synopsis>
1186 Parentheses are used to group subexpressions and override precedence.
1193 In addition to this list, there are a number of constructs that can
1194 be classified as an expression but do not follow any general syntax
1195 rules. These generally have the semantics of a function or
1196 operator and are explained in the appropriate location in <xref
1197 linkend="functions">. An example is the <literal>IS NULL</literal>
1202 We have already discussed constants in <xref
1203 linkend="sql-syntax-constants">. The following sections discuss
1204 the remaining options.
1208 <title>Column References</title>
1211 A column can be referenced in the form:
1213 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable> `['<replaceable>subscript</replaceable>`]'
1216 <replaceable>correlation</replaceable> is the name of a
1217 table (possibly qualified), or an alias for a table defined by means of a
1219 the key words <literal>NEW</literal> or <literal>OLD</literal>.
1220 (NEW and OLD can only appear in the action portion of a rule,
1221 while other correlation names can be used in any SQL statement.)
1222 The correlation name and separating dot may be omitted if the column name
1224 across all the tables being used in the current query. If
1225 <replaceable>column</replaceable> is of an array type, then the
1226 optional <replaceable>subscript</replaceable> selects a specific
1227 element or elements in the array. If no subscript is provided, then the
1228 whole array is selected. (See <xref linkend="arrays"> for more about
1234 <title>Positional Parameters</title>
1237 A positional parameter reference is used to indicate a parameter
1238 in an SQL function. Typically this is used in SQL function
1239 definition statements. The form of a parameter is:
1241 $<replaceable>number</replaceable>
1246 For example, consider the definition of a function,
1247 <function>dept</function>, as
1250 CREATE FUNCTION dept (text) RETURNS dept
1251 AS 'SELECT * FROM dept WHERE name = $1'
1255 Here the <literal>$1</literal> will be replaced by the first
1256 function argument when the function is invoked.
1261 <title>Operator Invocations</title>
1264 There are three possible syntaxes for an operator invocation:
1266 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
1267 <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
1268 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
1270 where the <replaceable>operator</replaceable> token follows the syntax
1271 rules of <xref linkend="sql-syntax-operators">, or is one of the
1272 keywords <token>AND</token>, <token>OR</token>, and
1273 <token>NOT</token>, or is a qualified operator name
1275 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operatorname</><literal>)</>
1277 Which particular operators exist and whether
1278 they are unary or binary depends on what operators have been
1279 defined by the system or the user. <xref linkend="functions">
1280 describes the built-in operators.
1285 <title>Function Calls</title>
1288 The syntax for a function call is the name of a function
1289 (possibly qualified with a schema name), followed by its argument list
1290 enclosed in parentheses:
1293 <replaceable>function</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> )
1298 For example, the following computes the square root of 2:
1305 The list of built-in functions is in <xref linkend="functions">.
1306 Other functions may be added by the user.
1310 <sect2 id="syntax-aggregates">
1311 <title>Aggregate Expressions</title>
1313 <indexterm zone="syntax-aggregates">
1314 <primary>aggregate functions</primary>
1318 An <firstterm>aggregate expression</firstterm> represents the
1319 application of an aggregate function across the rows selected by a
1320 query. An aggregate function reduces multiple inputs to a single
1321 output value, such as the sum or average of the inputs. The
1322 syntax of an aggregate expression is one of the following:
1325 <member><replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>)</member>
1326 <member><replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>)</member>
1327 <member><replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)</member>
1328 <member><replaceable>aggregate_name</replaceable> ( * )</member>
1331 where <replaceable>aggregate_name</replaceable> is a previously
1332 defined aggregate (possibly a qualified name), and
1333 <replaceable>expression</replaceable> is
1334 any value expression that does not itself contain an aggregate
1339 The first form of aggregate expression invokes the aggregate
1340 across all input rows for which the given expression yields a
1341 non-NULL value. (Actually, it is up to the aggregate function
1342 whether to ignore NULLs or not --- but all the standard ones do.)
1343 The second form is the same as the first, since
1344 <literal>ALL</literal> is the default. The third form invokes the
1345 aggregate for all distinct non-NULL values of the expression found
1346 in the input rows. The last form invokes the aggregate once for
1347 each input row regardless of NULL or non-NULL values; since no
1348 particular input value is specified, it is generally only useful
1349 for the <function>count()</function> aggregate function.
1353 For example, <literal>count(*)</literal> yields the total number
1354 of input rows; <literal>count(f1)</literal> yields the number of
1355 input rows in which <literal>f1</literal> is non-NULL;
1356 <literal>count(distinct f1)</literal> yields the number of
1357 distinct non-NULL values of <literal>f1</literal>.
1361 The predefined aggregate functions are described in <xref
1362 linkend="functions-aggregate">. Other aggregate functions may be added
1367 <sect2 id="sql-syntax-type-casts">
1368 <title>Type Casts</title>
1371 <primary>data types</primary>
1372 <secondary>type casts</secondary>
1376 A type cast specifies a conversion from one data type to another.
1377 <productname>PostgreSQL</productname> accepts two equivalent syntaxes
1380 CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> )
1381 <replaceable>expression</replaceable>::<replaceable>type</replaceable>
1383 The <literal>CAST</> syntax conforms to SQL92; the syntax with
1384 <literal>::</literal> is historical <productname>PostgreSQL</productname>
1389 When a cast is applied to a value expression of a known type, it
1390 represents a run-time type conversion. The cast will succeed only
1391 if a suitable type conversion function is available. Notice that this
1392 is subtly different from the use of casts with constants, as shown in
1393 <xref linkend="sql-syntax-constants-generic">. A cast applied to an
1394 unadorned string literal represents the initial assignment of a type
1395 to a literal constant value, and so it will succeed for any type
1396 (if the contents of the string literal are acceptable input syntax for the
1401 An explicit type cast may usually be omitted if there is no ambiguity as
1402 to the type that a value expression must produce (for example, when it is
1403 assigned to a table column); the system will automatically apply a
1404 type cast in such cases. However, automatic casting is only done for
1405 cast functions that are marked <quote>okay to apply implicitly</>
1406 in the system catalogs. Other cast functions must be invoked with
1407 explicit casting syntax. This restriction is intended to prevent
1408 surprising conversions from being applied silently.
1412 It is also possible to specify a type cast using a function-like
1415 <replaceable>typename</replaceable> ( <replaceable>expression</replaceable> )
1417 However, this only works for types whose names are also valid as
1418 function names. For example, <literal>double precision</literal>
1419 can't be used this way, but the equivalent <literal>float8</literal>
1420 can. Also, the names <literal>interval</>, <literal>time</>, and
1421 <literal>timestamp</> can only be used in this fashion if they are
1422 double-quoted, because of syntactic conflicts. Therefore, the use of
1423 the function-like cast syntax leads to inconsistencies and should
1424 probably be avoided in new applications.
1429 <title>Scalar Subqueries</title>
1432 A scalar subquery is an ordinary
1433 <command>SELECT</command> in parentheses that returns exactly one
1434 row with one column. The <command>SELECT</command> query is executed
1435 and the single returned value is used in the surrounding value expression.
1436 It is an error to use a query that
1437 returns more than one row or more than one column as a scalar subquery.
1438 (But if, during a particular execution, the subquery returns no rows,
1439 there is no error; the scalar result is taken to be NULL.)
1440 The subquery can refer to variables from the surrounding query,
1441 which will act as constants during any one evaluation of the subquery.
1442 See also <xref linkend="functions-subquery">.
1446 For example, the following finds the largest city population in each
1449 SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
1456 <title>Expression Evaluation</title>
1459 The order of evaluation of subexpressions is not defined. In
1460 particular, the inputs of an operator or function are not necessarily
1461 evaluated left-to-right or in any other fixed order.
1465 Furthermore, if the result of an expression can be determined by
1466 evaluating only some parts of it, then other subexpressions
1467 might not be evaluated at all. For instance, if one wrote
1469 SELECT true OR somefunc();
1471 then <literal>somefunc()</literal> would (probably) not be called
1472 at all. The same would be the case if one wrote
1474 SELECT somefunc() OR true;
1476 Note that this is not the same as the left-to-right
1477 <quote>short-circuiting</quote> of Boolean operators that is found
1478 in some programming languages.
1482 As a consequence, it is unwise to use functions with side effects
1483 as part of complex expressions. It is particularly dangerous to
1484 rely on side effects or evaluation order in WHERE and HAVING clauses,
1485 since those clauses are extensively reprocessed as part of
1486 developing an execution plan. Boolean
1487 expressions (AND/OR/NOT combinations) in those clauses may be reorganized
1488 in any manner allowed by the laws of Boolean algebra.
1492 When it is essential to force evaluation order, a CASE construct may
1493 be used. For example, this is an untrustworthy way of trying to
1494 avoid division by zero in a WHERE clause:
1496 SELECT ... WHERE x <> 0 AND y/x > 1.5;
1500 SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
1502 A CASE construct used in this fashion will defeat optimization attempts,
1503 so it should only be done when necessary.
1510 <!-- Keep this comment at the end of the file
1515 sgml-minimize-attributes:nil
1516 sgml-always-quote-attributes:t
1519 sgml-parent-document:nil
1520 sgml-default-dtd-file:"./reference.ced"
1521 sgml-exposed-tags:nil
1522 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1523 sgml-local-ecat-files:nil