1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="The SQL Language"
18 TITLE="The SQL Language"
21 TITLE="Value Expressions"
22 HREF="sql-expressions.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
60 HREF="sql.html#AEN997"
81 HREF="sql-expressions.html"
96 >Chapter 4. SQL Syntax</H1
102 >Table of Contents</B
106 HREF="sql-syntax.html#SQL-SYNTAX-LEXICAL"
107 >Lexical Structure</A
113 HREF="sql-syntax.html#SQL-SYNTAX-IDENTIFIERS"
114 >Identifiers and Key Words</A
118 HREF="sql-syntax.html#SQL-SYNTAX-CONSTANTS"
123 HREF="sql-syntax.html#SQL-SYNTAX-OPERATORS"
128 HREF="sql-syntax.html#AEN1269"
129 >Special Characters</A
133 HREF="sql-syntax.html#SQL-SYNTAX-COMMENTS"
138 HREF="sql-syntax.html#SQL-PRECEDENCE"
139 >Lexical Precedence</A
145 HREF="sql-expressions.html"
146 >Value Expressions</A
152 HREF="sql-expressions.html#AEN1510"
153 >Column References</A
157 HREF="sql-expressions.html#AEN1526"
158 >Positional Parameters</A
162 HREF="sql-expressions.html#AEN1540"
167 HREF="sql-expressions.html#AEN1559"
172 HREF="sql-expressions.html#AEN1570"
173 >Operator Invocations</A
177 HREF="sql-expressions.html#AEN1599"
182 HREF="sql-expressions.html#SYNTAX-AGGREGATES"
183 >Aggregate Expressions</A
187 HREF="sql-expressions.html#SQL-SYNTAX-TYPE-CASTS"
192 HREF="sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES"
193 >Scalar Subqueries</A
197 HREF="sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS"
198 >Array Constructors</A
202 HREF="sql-expressions.html#SYNTAX-EXPRESS-EVAL"
203 >Expression Evaluation Rules</A
213 > This chapter describes the syntax of SQL. It forms the foundation
214 for understanding the following chapters which will go into detail
215 about how the SQL commands are applied to define and modify data.
218 > We also advise users who are already familiar with SQL to read this
219 chapter carefully because there are several rules and concepts that
220 are implemented inconsistently among SQL databases or that are
231 NAME="SQL-SYNTAX-LEXICAL"
232 >4.1. Lexical Structure</A
238 > SQL input consists of a sequence of
242 >. A command is composed of a
250 >). The end of the input stream also
251 terminates a command. Which tokens are valid depends on the syntax
252 of the particular command.
255 > A token can be a <I
270 constant), or a special character symbol. Tokens are normally
271 separated by whitespace (space, tab, newline), but need not be if
272 there is no ambiguity (which is generally only the case if a
273 special character is adjacent to some other token type).
280 input. They are not tokens, they are effectively equivalent to
284 > For example, the following is (syntactically) valid SQL input:
286 CLASS="PROGRAMLISTING"
287 >SELECT * FROM MY_TABLE;
288 UPDATE MY_TABLE SET A = 5;
289 INSERT INTO MY_TABLE VALUES (3, 'hi there');</PRE
291 This is a sequence of three commands, one per line (although this
292 is not required; more than one command can be on a line, and
293 commands can usefully be split across lines).
296 > The SQL syntax is not very consistent regarding what tokens
297 identify commands and which are operands or parameters. The first
298 few tokens are generally the command name, so in the above example
299 we would usually speak of a <SPAN
313 > command always requires
317 > token to appear in a certain position, and
318 this particular variation of <TT
325 > in order to be complete. The
326 precise syntax rules for each command are described in <A
327 HREF="reference.html"
336 NAME="SQL-SYNTAX-IDENTIFIERS"
337 >4.1.1. Identifiers and Key Words</A
359 > in the example above are examples of
363 >, that is, words that have a fixed
364 meaning in the SQL language. The tokens <TT
375 >. They identify names of
376 tables, columns, or other database objects, depending on the
377 command they are used in. Therefore they are sometimes simply
381 >. Key words and identifiers have the
382 same lexical structure, meaning that one cannot know whether a
383 token is an identifier or a key word without knowing the language.
384 A complete list of key words can be found in <A
385 HREF="sql-keywords-appendix.html"
390 > SQL identifiers and key words must begin with a letter
397 >, but also letters with
398 diacritical marks and non-Latin letters) or an underscore
402 >). Subsequent characters in an identifier or
403 key word can be letters, underscores, digits
414 >). Note that dollar signs are not allowed in identifiers
415 according to the letter of the SQL standard, so their use may render
416 applications less portable.
417 The SQL standard will not define a key word that contains
418 digits or starts or ends with an underscore, so identifiers of this
419 form are safe against possible conflict with future extensions of the
427 The system uses no more than <TT
431 characters of an identifier; longer names can be written in
432 commands, but they will be truncated. By default,
436 > is 64 so the maximum identifier
437 length is 63. If this limit is problematic, it can be raised by
444 >src/include/postgres_ext.h</TT
452 Identifier and key word names are case insensitive. Therefore
454 CLASS="PROGRAMLISTING"
455 >UPDATE MY_TABLE SET A = 5;</PRE
457 can equivalently be written as
459 CLASS="PROGRAMLISTING"
460 >uPDaTE my_TabLE SeT a = 5;</PRE
462 A convention often used is to write key words in upper
463 case and names in lower case, e.g.,
465 CLASS="PROGRAMLISTING"
466 >UPDATE my_table SET a = 5;</PRE
474 There is a second kind of identifier: the <I
482 >. It is formed by enclosing an arbitrary
483 sequence of characters in double-quotes
488 identifier is always an identifier, never a key word. So
492 > could be used to refer to a column or
496 >, whereas an unquoted
500 > would be taken as a key word and
501 would therefore provoke a parse error when used where a table or
502 column name is expected. The example can be written with quoted
503 identifiers like this:
505 CLASS="PROGRAMLISTING"
506 >UPDATE "my_table" SET "a" = 5;</PRE
510 > Quoted identifiers can contain any character other than a double
511 quote itself. (To include a double quote, write two double quotes.)
512 This allows constructing table or column names that would
513 otherwise not be possible, such as ones containing spaces or
514 ampersands. The length limitation still applies.
517 > Quoting an identifier also makes it case-sensitive, whereas
518 unquoted names are always folded to lower case. For example, the
529 > are considered the same by
541 different from these three and each other. (The folding of
542 unquoted names to lower case in <SPAN
546 incompatible with the SQL standard, which says that unquoted names
547 should be folded to upper case. Thus, <TT
551 should be equivalent to <TT
558 > according to the standard. If you want
559 to write portable applications you are advised to always quote a
560 particular name or never quote it.)
568 NAME="SQL-SYNTAX-CONSTANTS"
575 > There are three kinds of <I
583 strings, bit strings, and numbers.
584 Constants can also be specified with explicit types, which can
585 enable more accurate representation and more efficient handling by
586 the system. The implicit constants are described below; explicit
587 constants are discussed afterwards.
594 NAME="SQL-SYNTAX-STRINGS"
595 >4.1.2.1. String Constants</A
605 A string constant in SQL is an arbitrary sequence of characters
606 bounded by single quotes (<TT
613 >. SQL allows single quotes to be embedded
614 in strings by typing two adjacent single quotes, e.g.,
617 >'Dianne''s horse'</TT
623 alternatively be escaped with a backslash (<TT
629 >'Dianne\'s horse'</TT
633 > C-style backslash escapes are also available:
637 > is a backspace, <TT
648 > is a carriage return, <TT
662 > is an octal number, is a
663 byte with the corresponding code. (It is your responsibility
664 that the byte sequences you create are valid characters in the
665 server character set encoding.) Any other character following a
666 backslash is taken literally. Thus, to include a backslash in a
667 string constant, type two backslashes.
670 > The character with the code zero cannot be in a string constant.
673 > Two string constants that are only separated by whitespace
678 >with at least one newline</I
681 and effectively treated as if the string had been written in one
682 constant. For example:
684 CLASS="PROGRAMLISTING"
690 CLASS="PROGRAMLISTING"
691 >SELECT 'foobar';</PRE
695 CLASS="PROGRAMLISTING"
696 >SELECT 'foo' 'bar';</PRE
698 is not valid syntax. (This slightly bizarre behavior is specified
706 following the standard.)
714 NAME="SQL-SYNTAX-BIT-STRINGS"
715 >4.1.2.2. Bit-String Constants</A
721 > Bit-string constants look like string constants with a
725 > (upper or lower case) immediately before the
726 opening quote (no intervening whitespace), e.g.,
730 >. The only characters allowed within
731 bit-string constants are <TT
741 > Alternatively, bit-string constants can be specified in hexadecimal
742 notation, using a leading <TT
745 > (upper or lower case),
749 >. This notation is equivalent to
750 a bit-string constant with four binary digits for each hexadecimal digit.
753 > Both forms of bit-string constant can be continued
754 across lines in the same way as regular string constants.
763 >4.1.2.3. Numeric Constants</A
769 > Numeric constants are accepted in these general forms:
828 > is one or more decimal
829 digits (0 through 9). At least one digit must be before or after the
830 decimal point, if one is used. At least one digit must follow the
834 >), if one is present.
835 There may not be any spaces or other characters embedded in the
836 constant. Note that any leading plus or minus sign is not actually
837 considered part of the constant; it is an operator applied to the
841 > These are some examples of valid numeric constants:
843 CLASS="LITERALLAYOUT"
865 A numeric constant that contains neither a decimal point nor an
866 exponent is initially presumed to be type <TT
870 value fits in type <TT
873 > (32 bits); otherwise it is
874 presumed to be type <TT
878 value fits in type <TT
881 > (64 bits); otherwise it is
885 >. Constants that contain decimal
886 points and/or exponents are always initially presumed to be type
893 > The initially assigned data type of a numeric constant is just a
894 starting point for the type resolution algorithms. In most cases
895 the constant will be automatically coerced to the most
896 appropriate type depending on context. When necessary, you can
897 force a numeric value to be interpreted as a specific data type
902 For example, you can force a numeric value to be treated as type
912 CLASS="PROGRAMLISTING"
913 >REAL '1.23' -- string style
914 1.23::REAL -- PostgreSQL (historical) style</PRE
923 NAME="SQL-SYNTAX-CONSTANTS-GENERIC"
924 >4.1.2.4. Constants of Other Types</A
930 > A constant of an <SPAN
937 entered using any one of the following notations:
962 The string's text is passed to the input conversion
963 routine for the type called <VAR
967 result is a constant of the indicated type. The explicit type
968 cast may be omitted if there is no ambiguity as to the type the
969 constant must be (for example, when it is passed as an argument
970 to a non-overloaded function), in which case it is automatically
974 > It is also possible to specify a type coercion using a function-like
986 but not all type names may be used in this way; see <A
987 HREF="sql-expressions.html#SQL-SYNTAX-TYPE-CASTS"
999 function-call syntaxes can also be used to specify run-time type
1000 conversions of arbitrary expressions, as discussed in <A
1001 HREF="sql-expressions.html#SQL-SYNTAX-TYPE-CASTS"
1014 can only be used to specify the type of a literal constant.
1015 Another restriction on
1026 is that it does not work for array types; use <TT
1033 > to specify the type of an array constant.
1042 NAME="SQL-SYNTAX-OPERATORS"
1043 >4.1.3. Operators</A
1049 > An operator name is a sequence of up to <TT
1053 (63 by default) characters from the following list:
1055 CLASS="LITERALLAYOUT"
1056 >+ - * / < > = ~ ! @ # % ^ & | ` ?</P
1059 There are a few restrictions on operator names, however:
1072 anywhere in an operator name, since they will be taken as the
1078 > A multiple-character operator name cannot end in <TT
1085 unless the name also contains at least one of these characters:
1087 CLASS="LITERALLAYOUT"
1088 >~ ! @ # % ^ & | ` ?</P
1093 > is an allowed operator name,
1097 > is not. This restriction allows
1101 > to parse SQL-compliant
1102 queries without requiring spaces between tokens.
1109 > When working with non-SQL-standard operator names, you will usually
1110 need to separate adjacent operators with spaces to avoid ambiguity.
1111 For example, if you have defined a left unary operator named <TT
1115 you cannot write <TT
1126 > reads it as two operator names
1136 >4.1.4. Special Characters</A
1139 > Some characters that are not alphanumeric have a special meaning
1140 that is different from being an operator. Details on the usage can
1141 be found at the location where the respective syntax element is
1142 described. This section only exists to advise the existence and
1143 summarize the purposes of these characters.
1150 > A dollar sign (<TT
1153 >) followed by digits is used
1154 to represent a positional parameter in the body of a function
1155 definition or a prepared statement. In other contexts the
1156 dollar sign may be part of an identifier.
1164 >) have their usual meaning to
1165 group expressions and enforce precedence. In some cases
1166 parentheses are required as part of the fixed syntax of a
1167 particular SQL command.
1175 >) are used to select the elements
1179 > for more information
1188 >) are used in some syntactical
1189 constructs to separate the elements of a list.
1194 > The semicolon (<TT
1197 >) terminates an SQL command.
1198 It cannot appear anywhere within a command, except within a
1199 string constant or quoted identifier.
1207 >) is used to select
1211 > from arrays. (See <A
1214 >.) In certain SQL dialects (such as Embedded
1215 SQL), the colon is used to prefix variable names.
1223 >) has a special meaning when
1227 > command or with the
1231 > aggregate function.
1239 >) is used in numeric
1240 constants, and to separate schema, table, and column names.
1253 NAME="SQL-SYNTAX-COMMENTS"
1260 > A comment is an arbitrary sequence of characters beginning with
1261 double dashes and extending to the end of the line, e.g.:
1263 CLASS="PROGRAMLISTING"
1264 >-- This is a standard SQL comment</PRE
1268 > Alternatively, C-style block comments can be used:
1270 CLASS="PROGRAMLISTING"
1271 >/* multiline comment
1272 * with nesting: /* nested block comment */
1275 where the comment begins with <TT
1279 the matching occurrence of <TT
1283 comments nest, as specified in the SQL standard but unlike C, so that one can
1284 comment out larger blocks of code that may contain existing block
1288 > A comment is removed from the input stream before further syntax
1289 analysis and is effectively replaced by whitespace.
1297 NAME="SQL-PRECEDENCE"
1298 >4.1.6. Lexical Precedence</A
1305 HREF="sql-syntax.html#SQL-PRECEDENCE-TABLE"
1307 > shows the precedence and
1308 associativity of the operators in <SPAN
1312 Most operators have the same precedence and are left-associative.
1313 The precedence and associativity of the operators is hard-wired
1314 into the parser. This may lead to non-intuitive behavior; for
1315 example the Boolean operators <TT
1322 > have a different precedence than the Boolean
1330 sometimes need to add parentheses when using combinations of
1331 binary and unary operators. For instance
1333 CLASS="PROGRAMLISTING"
1334 >SELECT 5 ! - 6;</PRE
1338 CLASS="PROGRAMLISTING"
1339 >SELECT 5 ! (- 6);</PRE
1341 because the parser has no idea -- until it is too late -- that
1345 > is defined as a postfix operator, not an infix one.
1346 To get the desired behavior in this case, you must write
1348 CLASS="PROGRAMLISTING"
1349 >SELECT (5 !) - 6;</PRE
1351 This is the price one pays for extensibility.
1356 NAME="SQL-PRECEDENCE-TABLE"
1360 >Table 4-1. Operator Precedence (decreasing)</B
1365 ><COL><COL><COL><THEAD
1368 >Operator/Element</TH
1385 >table/column name separator</TD
1399 >-style typecast</TD
1413 >array element selection</TD
1452 >multiplication, division, modulo</TD
1466 >addition, subtraction</TD
1511 >test for not null</TD
1519 >all other native and user-defined operators</TD
1552 >time interval overlap</TD
1569 >string pattern matching</TD
1583 >less than, greater than</TD
1594 >equality, assignment</TD
1605 >logical negation</TD
1616 >logical conjunction</TD
1627 >logical disjunction</TD
1633 > Note that the operator precedence rules also apply to user-defined
1634 operators that have the same names as the built-in operators
1635 mentioned above. For example, if you define a
1639 > operator for some custom data type it will have
1640 the same precedence as the built-in <SPAN
1644 matter what yours does.
1647 > When a schema-qualified operator name is used in the
1651 > syntax, as for example in
1653 CLASS="PROGRAMLISTING"
1654 >SELECT 3 OPERATOR(pg_catalog.+) 4;</PRE
1659 > construct is taken to have the default precedence
1661 HREF="sql-syntax.html#SQL-PRECEDENCE-TABLE"
1666 > operator. This is true no matter
1667 which specific operator name appears inside <TT
1680 SUMMARY="Footer navigation table"
1709 HREF="sql-expressions.html"
1719 >The SQL Language</TD
1733 >Value Expressions</TD