syntax.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

<!--
$PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.105 2005/11/04 23:14:02 petere Exp $
-->

<chapter id="sql-syntax">
 <title>SQL Syntax</title>

 <indexterm zone="sql-syntax">
  <primary>syntax</primary>
  <secondary>SQL</secondary>
 </indexterm>

 <para>
  This chapter describes the syntax of SQL.  It forms the foundation
  for understanding the following chapters which will go into detail
  about how the SQL commands are applied to define and modify data.
 </para>

 <para>
  We also advise users who are already familiar with SQL to read this
  chapter carefully because there are several rules and concepts that
  are implemented inconsistently among SQL databases or that are
  specific to <productname>PostgreSQL</productname>.
 </para>

 <sect1 id="sql-syntax-lexical">
  <title>Lexical Structure</title>

  <indexterm>
   <primary>token</primary>
  </indexterm>

  <para>
   SQL input consists of a sequence of
   <firstterm>commands</firstterm>.  A command is composed of a
   sequence of <firstterm>tokens</firstterm>, terminated by a
   semicolon (<quote>;</quote>).  The end of the input stream also
   terminates a command.  Which tokens are valid depends on the syntax
   of the particular command.
  </para>

  <para>
   A token can be a <firstterm>key word</firstterm>, an
   <firstterm>identifier</firstterm>, a <firstterm>quoted
   identifier</firstterm>, a <firstterm>literal</firstterm> (or
   constant), or a special character symbol.  Tokens are normally
   separated by whitespace (space, tab, newline), but need not be if
   there is no ambiguity (which is generally only the case if a
   special character is adjacent to some other token type).
  </para>

  <para>
   Additionally, <firstterm>comments</firstterm> can occur in SQL
   input.  They are not tokens, they are effectively equivalent to
   whitespace.
  </para>

   <para>
    For example, the following is (syntactically) valid SQL input:
<programlisting>
SELECT * FROM MY_TABLE;
UPDATE MY_TABLE SET A = 5;
INSERT INTO MY_TABLE VALUES (3, 'hi there');
</programlisting>
    This is a sequence of three commands, one per line (although this
    is not required; more than one command can be on a line, and
    commands can usefully be split across lines).
   </para>

  <para>
   The SQL syntax is not very consistent regarding what tokens
   identify commands and which are operands or parameters.  The first
   few tokens are generally the command name, so in the above example
   we would usually speak of a <quote>SELECT</quote>, an
   <quote>UPDATE</quote>, and an <quote>INSERT</quote> command.  But
   for instance the <command>UPDATE</command> command always requires
   a <token>SET</token> token to appear in a certain position, and
   this particular variation of <command>INSERT</command> also
   requires a <token>VALUES</token> in order to be complete.  The
   precise syntax rules for each command are described in <xref linkend="reference">.
  </para>

  <sect2 id="sql-syntax-identifiers">
   <title>Identifiers and Key Words</title>

   <indexterm zone="sql-syntax-identifiers">
    <primary>identifier</primary>
    <secondary>syntax of</secondary>
   </indexterm>

   <indexterm zone="sql-syntax-identifiers">
    <primary>name</primary>
    <secondary>syntax of</secondary>
   </indexterm>

   <indexterm zone="sql-syntax-identifiers">
    <primary>key word</primary>
    <secondary>syntax of</secondary>
   </indexterm>

   <para>
    Tokens such as <token>SELECT</token>, <token>UPDATE</token>, or
    <token>VALUES</token> in the example above are examples of
    <firstterm>key words</firstterm>, that is, words that have a fixed
    meaning in the SQL language.  The tokens <token>MY_TABLE</token>
    and <token>A</token> are examples of
    <firstterm>identifiers</firstterm>.  They identify names of
    tables, columns, or other database objects, depending on the
    command they are used in.  Therefore they are sometimes simply
    called <quote>names</quote>.  Key words and identifiers have the
    same lexical structure, meaning that one cannot know whether a
    token is an identifier or a key word without knowing the language.
    A complete list of key words can be found in <xref
    linkend="sql-keywords-appendix">.
   </para>

   <para>
    SQL identifiers and key words must begin with a letter
    (<literal>a</literal>-<literal>z</literal>, but also letters with
    diacritical marks and non-Latin letters) or an underscore
    (<literal>_</literal>).  Subsequent characters in an identifier or
    key word can be letters, underscores, digits
    (<literal>0</literal>-<literal>9</literal>), or dollar signs
    (<literal>$</>).  Note that dollar signs are not allowed in identifiers
    according to the letter of the SQL standard, so their use may render
    applications less portable.
    The SQL standard will not define a key word that contains
    digits or starts or ends with an underscore, so identifiers of this
    form are safe against possible conflict with future extensions of the
    standard.
   </para>

   <para>
    <indexterm><primary>identifier</primary><secondary>length</secondary></indexterm>
    The system uses no more than <symbol>NAMEDATALEN</symbol>-1
    characters of an identifier; longer names can be written in
    commands, but they will be truncated.  By default,
    <symbol>NAMEDATALEN</symbol> is 64 so the maximum identifier
    length is 63. If this limit is problematic, it can be raised by
    changing the <symbol>NAMEDATALEN</symbol> constant in
    <filename>src/include/postgres_ext.h</filename>.
   </para>

   <para>
    <indexterm>
     <primary>case sensitivity</primary>
     <secondary>of SQL commands</secondary>
    </indexterm>
    Identifier and key word names are case insensitive.  Therefore
<programlisting>
UPDATE MY_TABLE SET A = 5;
</programlisting>
    can equivalently be written as
<programlisting>
uPDaTE my_TabLE SeT a = 5;
</programlisting>
    A convention often used is to write key words in upper
    case and names in lower case, e.g.,
<programlisting>
UPDATE my_table SET a = 5;
</programlisting>
   </para>

   <para>
    <indexterm>
     <primary>quotation marks</primary>
     <secondary>and identifiers</secondary>
    </indexterm>
    There is a second kind of identifier:  the <firstterm>delimited
    identifier</firstterm> or <firstterm>quoted
    identifier</firstterm>.  It is formed by enclosing an arbitrary
    sequence of characters in double-quotes
    (<literal>"</literal>). <!-- " font-lock mania --> A delimited
    identifier is always an identifier, never a key word.  So
    <literal>"select"</literal> could be used to refer to a column or
    table named <quote>select</quote>, whereas an unquoted
    <literal>select</literal> would be taken as a key word and
    would therefore provoke a parse error when used where a table or
    column name is expected.  The example can be written with quoted
    identifiers like this:
<programlisting>
UPDATE "my_table" SET "a" = 5;
</programlisting>
   </para>

   <para>
    Quoted identifiers can contain any character other than a double
    quote itself.  (To include a double quote, write two double quotes.)
    This allows constructing table or column names that would
    otherwise not be possible, such as ones containing spaces or
    ampersands.  The length limitation still applies.
   </para>

   <para>
    Quoting an identifier also makes it case-sensitive, whereas
    unquoted names are always folded to lower case.  For example, the
    identifiers <literal>FOO</literal>, <literal>foo</literal>, and
    <literal>"foo"</literal> are considered the same by
    <productname>PostgreSQL</productname>, but
    <literal>"Foo"</literal> and <literal>"FOO"</literal> are
    different from these three and each other.  (The folding of
    unquoted names to lower case in <productname>PostgreSQL</> is
    incompatible with the SQL standard, which says that unquoted names
    should be folded to upper case.  Thus, <literal>foo</literal>
    should be equivalent to <literal>"FOO"</literal> not
    <literal>"foo"</literal> according to the standard.  If you want
    to write portable applications you are advised to always quote a
    particular name or never quote it.)
   </para>
  </sect2>


  <sect2 id="sql-syntax-constants">
   <title>Constants</title>

   <indexterm zone="sql-syntax-constants">
    <primary>constant</primary>
   </indexterm>

   <para>
    There are three kinds of <firstterm>implicitly-typed
    constants</firstterm> in <productname>PostgreSQL</productname>:
    strings, bit strings, and numbers.
    Constants can also be specified with explicit types, which can
    enable more accurate representation and more efficient handling by
    the system. These alternatives are discussed in the following
    subsections.
   </para>

   <sect3 id="sql-syntax-strings">
    <title>String Constants</title>

    <indexterm zone="sql-syntax-strings">
     <primary>character string</primary>
     <secondary>constant</secondary>
    </indexterm>

    <para>
     <indexterm>
      <primary>quotation marks</primary>
      <secondary>escaping</secondary>
     </indexterm>
     A string constant in SQL is an arbitrary sequence of characters
     bounded by single quotes (<literal>'</literal>), for example
     <literal>'This is a string'</literal>.  The standard-compliant way of
     writing a single-quote character within a string constant is to
     write two adjacent single quotes, e.g.
     <literal>'Dianne''s horse'</literal>.
     <productname>PostgreSQL</productname> also allows single quotes
     to be escaped with a backslash (<literal>\'</literal>).  However,
     future versions of <productname>PostgreSQL</productname> will not
     allow this, so applications using backslashes should convert to the 
     standard-compliant method outlined above.
    </para>

    <para>
     Another <productname>PostgreSQL</productname> extension is that
     C-style backslash escapes are available: <literal>\b</literal> is a
     backspace, <literal>\f</literal> is a form feed,
     <literal>\n</literal> is a newline, <literal>\r</literal> is a
     carriage return, <literal>\t</literal> is a tab. Also supported is
     <literal>\<replaceable>digits</replaceable></literal>, where
     <replaceable>digits</replaceable> represents an octal byte value, and
     <literal>\x<replaceable>hexdigits</replaceable></literal>, where
     <replaceable>hexdigits</replaceable> represents a hexadecimal byte value.
     (It is your responsibility that the byte sequences you create are
     valid characters in the server character set encoding.) Any other
     character following a backslash is taken literally. Thus, to
     include a backslash in a string constant, write two backslashes.
    </para>

    <note>
    <para>
     While ordinary strings now support C-style backslash escapes,
     future versions will generate warnings for such usage and
     eventually treat backslashes as literal characters to be
     standard-conforming. The proper way to specify escape processing is
     to use the escape string syntax to indicate that escape
     processing is desired. Escape string syntax is specified by writing
     the letter <literal>E</literal> (upper or lower case) just before
     the string, e.g. <literal>E'\041'</>. This method will work in all
     future versions of <productname>PostgreSQL</productname>.
    </para>
    </note>

    <para>
     The character with the code zero cannot be in a string constant.
    </para>

    <para>
     Two string constants that are only separated by whitespace
     <emphasis>with at least one newline</emphasis> are concatenated
     and effectively treated as if the string had been written in one
     constant.  For example:
<programlisting>
SELECT 'foo'
'bar';
</programlisting>
     is equivalent to
<programlisting>
SELECT 'foobar';
</programlisting>
     but
<programlisting>
SELECT 'foo'      'bar';
</programlisting>
     is not valid syntax.  (This slightly bizarre behavior is specified
     by <acronym>SQL</acronym>; <productname>PostgreSQL</productname> is
     following the standard.)
    </para>
   </sect3>

   <sect3 id="sql-syntax-dollar-quoting">
    <title>Dollar-Quoted String Constants</title>

     <indexterm>
      <primary>dollar quoting</primary>
     </indexterm>

    <para>
     While the standard syntax for specifying string constants is usually
     convenient, it can be difficult to understand when the desired string
     contains many single quotes or backslashes, since each of those must
     be doubled. To allow more readable queries in such situations,
     <productname>PostgreSQL</productname> provides another way, called
     <quote>dollar quoting</quote>, to write string constants.
     A dollar-quoted string constant
     consists of a dollar sign (<literal>$</literal>), an optional
     <quote>tag</quote> of zero or more characters, another dollar
     sign, an arbitrary sequence of characters that makes up the
     string content, a dollar sign, the same tag that began this
     dollar quote, and a dollar sign. For example, here are two
     different ways to specify the string <quote>Dianne's horse</>
     using dollar quoting:
<programlisting>
$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$
</programlisting>
     Notice that inside the dollar-quoted string, single quotes can be
     used without needing to be escaped.  Indeed, no characters inside
     a dollar-quoted string are ever escaped: the string content is always
     written literally.  Backslashes are not special, and neither are
     dollar signs, unless they are part of a sequence matching the opening
     tag.
    </para>

    <para>
     It is possible to nest dollar-quoted string constants by choosing
     different tags at each nesting level.  This is most commonly used in
     writing function definitions.  For example:
<programlisting>
$function$
BEGIN
    RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
END;
$function$
</programlisting>
     Here, the sequence <literal>$q$[\t\r\n\v\\]$q$</> represents a
     dollar-quoted literal string <literal>[\t\r\n\v\\]</>, which will
     be recognized when the function body is executed by
     <productname>PostgreSQL</>.  But since the sequence does not match
     the outer dollar quoting delimiter <literal>$function$</>, it is
     just some more characters within the constant so far as the outer
     string is concerned.
    </para>

    <para>
     The tag, if any, of a dollar-quoted string follows the same rules
     as an unquoted identifier, except that it cannot contain a dollar sign.
     Tags are case sensitive, so <literal>$tag$String content$tag$</literal>
     is correct, but <literal>$TAG$String content$tag$</literal> is not.
    </para>

    <para>
     A dollar-quoted string that follows a keyword or identifier must
     be separated from it by whitespace; otherwise the dollar quoting
     delimiter would be taken as part of the preceding identifier.
    </para>

    <para>
     Dollar quoting is not part of the SQL standard, but it is often a more
     convenient way to write complicated string literals than the
     standard-compliant single quote syntax.  It is particularly useful when
     representing string constants inside other constants, as is often needed
     in procedural function definitions.  With single-quote syntax, each
     backslash in the above example would have to be written as four
     backslashes, which would be reduced to two backslashes in parsing the
     original string constant, and then to one when the inner string constant
     is re-parsed during function execution.
    </para>
   </sect3>

   <sect3 id="sql-syntax-bit-strings">
    <title>Bit-String Constants</title>

    <indexterm zone="sql-syntax-bit-strings">
     <primary>bit string</primary>
     <secondary>constant</secondary>
    </indexterm>

    <para>
     Bit-string constants look like regular string constants with a
     <literal>B</literal> (upper or lower case) immediately before the
     opening quote (no intervening whitespace), e.g.,
     <literal>B'1001'</literal>.  The only characters allowed within
     bit-string constants are <literal>0</literal> and
     <literal>1</literal>.
    </para>

    <para>
     Alternatively, bit-string constants can be specified in hexadecimal
     notation, using a leading <literal>X</literal> (upper or lower case),
     e.g., <literal>X'1FF'</literal>.  This notation is equivalent to
     a bit-string constant with four binary digits for each hexadecimal digit.
    </para>

    <para>
     Both forms of bit-string constant can be continued
     across lines in the same way as regular string constants.
     Dollar quoting cannot be used in a bit-string constant.
    </para>
   </sect3>

   <sect3>
    <title>Numeric Constants</title>

    <indexterm>
     <primary>number</primary>