2 $Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.64 2002/08/05 19:43:31 petere Exp $
5 <chapter id="sql-syntax">
6 <title>SQL Syntax</title>
8 <indexterm zone="sql-syntax">
9 <primary>syntax</primary>
10 <secondary>SQL</secondary>
14 This chapter describes the syntax of SQL. It forms the foundation
15 for understanding the following chapters which will go into detail
16 about how the SQL commands are applied to define and modify data.
20 We also advise users who are already familiar with SQL to read this
21 chapter carefully because there are several rules and concepts that
22 are implemented inconsistently among SQL databases or that are
23 specific to <productname>PostgreSQL</productname>.
26 <sect1 id="sql-syntax-lexical">
27 <title>Lexical Structure</title>
30 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></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.
807 <sect1 id="sql-naming">
808 <title>Schemas and Naming Conventions</title>
811 <primary>schemas</primary>
815 <primary>search path</primary>
819 <primary>namespaces</primary>
823 A <productname>PostgreSQL</productname> database cluster (installation)
824 contains one or more named databases. Users and groups of users are
825 shared across the entire cluster, but no other data is shared across
826 databases. Any given client connection to the server can access
827 only the data in a single database, the one specified in the connection
833 Users of a cluster do not necessarily have the privilege to access every
834 database in the cluster. Sharing of user names means that there
835 cannot be different users named, say, <literal>joe</> in two databases
836 in the same cluster; but the system can be configured to allow
837 <literal>joe</> access to only some of the databases.
842 <title>Schema Object Names</title>
845 A database contains one or more named <firstterm>schemas</>, which
846 in turn contain tables. Schemas also contain other kinds of named
847 objects, including datatypes, functions, and operators. The same
848 object name can be used in different schemas without conflict; for
849 example, both <literal>schema1</> and <literal>myschema</> may
850 contain tables named <literal>mytable</>. Unlike databases, schemas
851 are not rigidly separated: a user may access objects in any of the
852 schemas in the database he is connected to, if he has privileges
857 <primary>qualified names</primary>
861 <primary>names</primary>
862 <secondary>qualified</secondary>
866 To name a table precisely, write a <firstterm>qualified name</> consisting
867 of the schema name and table name separated by a dot:
869 <replaceable>schema</><literal>.</><replaceable>table</>
871 Actually, the even more general syntax
873 <replaceable>database</><literal>.</><replaceable>schema</><literal>.</><replaceable>table</>
875 can be used too, but at present this is just for pro-forma compliance
876 with the SQL standard; if you write a database name it must be the
877 same as the database you are connected to.
881 <primary>unqualified names</primary>
885 <primary>names</primary>
886 <secondary>unqualified</secondary>
890 Qualified names are tedious to write, and it's often best not to
891 wire a particular schema name into applications anyway. Therefore
892 tables are often referred to by <firstterm>unqualified names</>,
893 which consist of just the table name. The system determines which table
894 is meant by following a <firstterm>search path</>, which is a list
895 of schemas to look in. The first matching table in the search path
896 is taken to be the one wanted. If there is no match in the search
897 path, an error is reported, even if matching table names exist
898 in other schemas in the database.
902 The first schema named in the search path is called the current schema.
903 Aside from being the first schema searched, it is also the schema in
904 which new tables will be created if the <command>CREATE TABLE</>
905 command does not specify a schema name.
909 The search path works in the same way for datatype names, function names,
910 and operator names as it does for table names. Datatype and function
911 names can be qualified in exactly the same way as table names. If you
912 need to write a qualified operator name in an expression, there is a
913 special provision: you must write
915 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operator</><literal>)</>
917 This is needed to avoid syntactic ambiguity. An example is
919 SELECT 3 OPERATOR(pg_catalog.+) 4;
921 In practice one usually relies on the search path for operators,
922 so as not to have to write anything so ugly as that.
926 The standard search path in <productname>PostgreSQL</productname>
927 contains first the schema having the same name as the session user
928 (if it exists), and second the schema named <literal>public</>
929 (if it exists, which it does by default). This arrangement allows
930 a flexible combination of private and shared tables. If no per-user
931 schemas are created then all user tables will exist in the shared
932 <literal>public</> schema, providing behavior that is backwards-compatible
933 with pre-7.3 <productname>PostgreSQL</productname> releases.
938 There is no concept of a <literal>public</> schema in the SQL standard.
939 To achieve closest conformance to the standard, the DBA should
940 create per-user schemas for every user, and not use (perhaps even
941 remove) the <literal>public</> schema.
946 In addition to <literal>public</> and user-created schemas, each database
948 <literal>pg_catalog</> schema, which contains the system tables
949 and all the built-in datatypes, functions, and operators.
950 <literal>pg_catalog</> is always effectively part of the search path.
951 If it is not named explicitly in the path then it is implicitly searched
952 <emphasis>before</> searching the path's schemas. This ensures that
953 built-in names will always be findable. However, you may explicitly
954 place <literal>pg_catalog</> at the end of your search path if you
955 prefer to have user-defined names override built-in names.
959 <sect2 id="sql-reserved-names">
960 <title>Reserved names</title>
963 <primary>reserved names</primary>
967 <primary>names</primary>
968 <secondary>reserved</secondary>
972 There are several restrictions on the names that can be chosen for
973 user-defined database objects. These restrictions vary depending
974 on the kind of object. (Note that these restrictions are
975 separate from whether the name is a key word or not; quoting a
976 name will not allow you to escape these restrictions.)
980 Schema names beginning with <literal>pg_</> are reserved for system
981 purposes and may not be created by users.
985 In <productname>PostgreSQL</productname> versions before 7.3, table
986 names beginning with <literal>pg_</> were reserved. This is no longer
987 true: you may create such a table name if you wish, in any non-system
988 schema. However, it's best to continue to avoid such names,
989 to ensure that you won't suffer a conflict if some future version
990 defines a system catalog named the same as your table. (With the
991 default search path, an unqualified reference to your table name
992 would be resolved as the system catalog instead.) System catalogs will
993 continue to follow the convention of having names beginning with
994 <literal>pg_</>, so that they will not conflict with unqualified
995 user-table names so long as users avoid the <literal>pg_</> prefix.
999 Every table has several <firstterm>system columns</> that are
1000 implicitly defined by the system. Therefore, these names cannot
1001 be used as names of user-defined columns:
1004 <primary>columns</primary>
1005 <secondary>system columns</secondary>
1010 <term><structfield>oid</></term>
1014 <primary>OID</primary>
1016 The object identifier (object ID) of a row. This is a serial number
1017 that is automatically added by <productname>PostgreSQL</productname> to all table rows (unless
1018 the table was created WITHOUT OIDS, in which case this column is
1019 not present). See <xref linkend="datatype-oid"> for more info.
1025 <term><structfield>tableoid</></term>
1028 The OID of the table containing this row. This attribute is
1029 particularly handy for queries that select from inheritance
1030 hierarchies, since without it, it's difficult to tell which
1031 individual table a row came from. The
1032 <structfield>tableoid</structfield> can be joined against the
1033 <structfield>oid</structfield> column of
1034 <classname>pg_class</classname> to obtain the table name.
1040 <term><structfield>xmin</></term>
1043 The identity (transaction ID) of the inserting transaction for
1044 this tuple. (Note: A tuple is an individual state of a row;
1045 each update of a row creates a new tuple for the same logical row.)
1051 <term><structfield>cmin</></term>
1054 The command identifier (starting at zero) within the inserting
1061 <term><structfield>xmax</></term>
1064 The identity (transaction ID) of the deleting transaction,
1065 or zero for an undeleted tuple. It is possible for this field
1066 to be nonzero in a visible tuple: that usually indicates that the
1067 deleting transaction hasn't committed yet, or that an attempted
1068 deletion was rolled back.
1074 <term><structfield>cmax</></term>
1077 The command identifier within the deleting transaction, or zero.
1083 <term><structfield>ctid</></term>
1086 The physical location of the tuple within its table.
1087 Note that although the <structfield>ctid</structfield>
1088 can be used to locate the tuple very quickly, a row's
1089 <structfield>ctid</structfield> will change each time it is updated
1090 or moved by <command>VACUUM FULL</>.
1091 Therefore <structfield>ctid</structfield> is useless as a long-term
1093 The OID, or even better a user-defined serial number, should
1094 be used to identify logical rows.
1103 <sect1 id="sql-expressions">
1104 <title>Value Expressions</title>
1107 Value expressions are used in a variety of contexts, such
1108 as in the target list of the <command>SELECT</command> command, as
1109 new column values in <command>INSERT</command> or
1110 <command>UPDATE</command>, or in search conditions in a number of
1111 commands. The result of a value expression is sometimes called a
1112 <firstterm>scalar</firstterm>, to distinguish it from the result of
1113 a table expression (which is a table). Value expressions are
1114 therefore also called <firstterm>scalar expressions</firstterm> (or
1115 even simply <firstterm>expressions</firstterm>). The expression
1116 syntax allows the calculation of values from primitive parts using
1117 arithmetic, logical, set, and other operations.
1121 A value expression is one of the following:
1126 A constant or literal value; see <xref linkend="sql-syntax-constants">.
1138 A positional parameter reference, in the body of a function declaration.
1144 An operator invocation.
1156 An aggregate expression.
1173 <synopsis>( <replaceable>expression</replaceable> )</synopsis>
1175 Parentheses are used to group subexpressions and override precedence.
1182 In addition to this list, there are a number of constructs that can
1183 be classified as an expression but do not follow any general syntax
1184 rules. These generally have the semantics of a function or
1185 operator and are explained in the appropriate location in <xref
1186 linkend="functions">. An example is the <literal>IS NULL</literal>
1191 We have already discussed constants in <xref
1192 linkend="sql-syntax-constants">. The following sections discuss
1193 the remaining options.
1197 <title>Column References</title>
1200 A column can be referenced in the form:
1202 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable> `['<replaceable>subscript</replaceable>`]'
1205 <replaceable>correlation</replaceable> is the name of a
1206 table (possibly qualified), or an alias for a table defined by means of a
1208 the key words <literal>NEW</literal> or <literal>OLD</literal>.
1209 (NEW and OLD can only appear in the action portion of a rule,
1210 while other correlation names can be used in any SQL statement.)
1211 The correlation name and separating dot may be omitted if the column name
1213 across all the tables being used in the current query. If
1214 <replaceable>column</replaceable> is of an array type, then the
1215 optional <replaceable>subscript</replaceable> selects a specific
1216 element or elements in the array. If no subscript is provided, then the
1217 whole array is selected. (See <xref linkend="arrays"> for more about
1223 <title>Positional Parameters</title>
1226 A positional parameter reference is used to indicate a parameter
1227 in an SQL function. Typically this is used in SQL function
1228 definition statements. The form of a parameter is:
1230 $<replaceable>number</replaceable>
1235 For example, consider the definition of a function,
1236 <function>dept</function>, as
1239 CREATE FUNCTION dept (text) RETURNS dept
1240 AS 'SELECT * FROM dept WHERE name = $1'
1244 Here the <literal>$1</literal> will be replaced by the first
1245 function argument when the function is invoked.
1250 <title>Operator Invocations</title>
1253 There are three possible syntaxes for an operator invocation:
1255 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
1256 <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
1257 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
1259 where the <replaceable>operator</replaceable> token follows the syntax
1260 rules of <xref linkend="sql-syntax-operators">, or is one of the
1261 keywords <token>AND</token>, <token>OR</token>, and
1262 <token>NOT</token>, or is a qualified operator name
1264 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operatorname</><literal>)</>
1266 Which particular operators exist and whether
1267 they are unary or binary depends on what operators have been
1268 defined by the system or the user. <xref linkend="functions">
1269 describes the built-in operators.
1274 <title>Function Calls</title>
1277 The syntax for a function call is the name of a function
1278 (possibly qualified with a schema name), followed by its argument list
1279 enclosed in parentheses:
1282 <replaceable>function</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> )
1287 For example, the following computes the square root of 2:
1294 The list of built-in functions is in <xref linkend="functions">.
1295 Other functions may be added by the user.
1299 <sect2 id="syntax-aggregates">
1300 <title>Aggregate Expressions</title>
1302 <indexterm zone="syntax-aggregates">
1303 <primary>aggregate functions</primary>
1307 An <firstterm>aggregate expression</firstterm> represents the
1308 application of an aggregate function across the rows selected by a
1309 query. An aggregate function reduces multiple inputs to a single
1310 output value, such as the sum or average of the inputs. The
1311 syntax of an aggregate expression is one of the following:
1314 <member><replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>)</member>
1315 <member><replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>)</member>
1316 <member><replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)</member>
1317 <member><replaceable>aggregate_name</replaceable> ( * )</member>
1320 where <replaceable>aggregate_name</replaceable> is a previously
1321 defined aggregate (possibly a qualified name), and
1322 <replaceable>expression</replaceable> is
1323 any value expression that does not itself contain an aggregate
1328 The first form of aggregate expression invokes the aggregate
1329 across all input rows for which the given expression yields a
1330 non-NULL value. (Actually, it is up to the aggregate function
1331 whether to ignore NULLs or not --- but all the standard ones do.)
1332 The second form is the same as the first, since
1333 <literal>ALL</literal> is the default. The third form invokes the
1334 aggregate for all distinct non-NULL values of the expression found
1335 in the input rows. The last form invokes the aggregate once for
1336 each input row regardless of NULL or non-NULL values; since no
1337 particular input value is specified, it is generally only useful
1338 for the <function>count()</function> aggregate function.
1342 For example, <literal>count(*)</literal> yields the total number
1343 of input rows; <literal>count(f1)</literal> yields the number of
1344 input rows in which <literal>f1</literal> is non-NULL;
1345 <literal>count(distinct f1)</literal> yields the number of
1346 distinct non-NULL values of <literal>f1</literal>.
1350 The predefined aggregate functions are described in <xref
1351 linkend="functions-aggregate">. Other aggregate functions may be added
1356 <sect2 id="sql-syntax-type-casts">
1357 <title>Type Casts</title>
1360 <primary>data types</primary>
1361 <secondary>type casts</secondary>
1365 A type cast specifies a conversion from one data type to another.
1366 <productname>PostgreSQL</productname> accepts two equivalent syntaxes
1369 CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> )
1370 <replaceable>expression</replaceable>::<replaceable>type</replaceable>
1372 The <literal>CAST</> syntax conforms to SQL92; the syntax with
1373 <literal>::</literal> is historical <productname>PostgreSQL</productname>
1378 When a cast is applied to a value expression of a known type, it
1379 represents a run-time type conversion. The cast will succeed only
1380 if a suitable type conversion function is available. Notice that this
1381 is subtly different from the use of casts with constants, as shown in
1382 <xref linkend="sql-syntax-constants-generic">. A cast applied to an
1383 unadorned string literal represents the initial assignment of a type
1384 to a literal constant value, and so it will succeed for any type
1385 (if the contents of the string literal are acceptable input syntax for the
1390 An explicit type cast may usually be omitted if there is no ambiguity as
1391 to the type that a value expression must produce (for example, when it is
1392 assigned to a table column); the system will automatically apply a
1393 type cast in such cases. However, automatic casting is only done for
1394 cast functions that are marked <quote>okay to apply implicitly</>
1395 in the system catalogs. Other cast functions must be invoked with
1396 explicit casting syntax. This restriction is intended to prevent
1397 surprising conversions from being applied silently.
1401 It is also possible to specify a type cast using a function-like
1404 <replaceable>typename</replaceable> ( <replaceable>expression</replaceable> )
1406 However, this only works for types whose names are also valid as
1407 function names. For example, <literal>double precision</literal>
1408 can't be used this way, but the equivalent <literal>float8</literal>
1409 can. Also, the names <literal>interval</>, <literal>time</>, and
1410 <literal>timestamp</> can only be used in this fashion if they are
1411 double-quoted, because of syntactic conflicts. Therefore, the use of
1412 the function-like cast syntax leads to inconsistencies and should
1413 probably be avoided in new applications.
1418 <title>Scalar Subqueries</title>
1421 A scalar subquery is an ordinary
1422 <command>SELECT</command> in parentheses that returns exactly one
1423 row with one column. The <command>SELECT</command> query is executed
1424 and the single returned value is used in the surrounding value expression.
1425 It is an error to use a query that
1426 returns more than one row or more than one column as a scalar subquery.
1427 (But if, during a particular execution, the subquery returns no rows,
1428 there is no error; the scalar result is taken to be NULL.)
1429 The subquery can refer to variables from the surrounding query,
1430 which will act as constants during any one evaluation of the subquery.
1431 See also <xref linkend="functions-subquery">.
1435 For example, the following finds the largest city population in each
1438 SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
1445 <title>Expression Evaluation</title>
1448 The order of evaluation of subexpressions is not defined. In
1449 particular, the inputs of an operator or function are not necessarily
1450 evaluated left-to-right or in any other fixed order.
1454 Furthermore, if the result of an expression can be determined by
1455 evaluating only some parts of it, then other subexpressions
1456 might not be evaluated at all. For instance, if one wrote
1458 SELECT true OR somefunc();
1460 then <literal>somefunc()</literal> would (probably) not be called
1461 at all. The same would be the case if one wrote
1463 SELECT somefunc() OR true;
1465 Note that this is not the same as the left-to-right
1466 <quote>short-circuiting</quote> of Boolean operators that is found
1467 in some programming languages.
1471 As a consequence, it is unwise to use functions with side effects
1472 as part of complex expressions. It is particularly dangerous to
1473 rely on side effects or evaluation order in WHERE and HAVING clauses,
1474 since those clauses are extensively reprocessed as part of
1475 developing an execution plan. Boolean
1476 expressions (AND/OR/NOT combinations) in those clauses may be reorganized
1477 in any manner allowed by the laws of Boolean algebra.
1481 When it is essential to force evaluation order, a CASE construct may
1482 be used. For example, this is an untrustworthy way of trying to
1483 avoid division by zero in a WHERE clause:
1485 SELECT ... WHERE x <> 0 AND y/x > 1.5;
1489 SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
1491 A CASE construct used in this fashion will defeat optimization attempts,
1492 so it should only be done when necessary.
1499 <!-- Keep this comment at the end of the file
1504 sgml-minimize-attributes:nil
1505 sgml-always-quote-attributes:t
1508 sgml-parent-document:nil
1509 sgml-default-dtd-file:"./reference.ced"
1510 sgml-exposed-tags:nil
1511 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1512 sgml-local-ecat-files:nil