syntax.sgml

PostgreSQL7.4.6 for Linux

    <para>
     The initially assigned data type of a numeric constant is just a
     starting point for the type resolution algorithms.  In most cases
     the constant will be automatically coerced to the most
     appropriate type depending on context.  When necessary, you can
     force a numeric value to be interpreted as a specific data type
     by casting it.<indexterm><primary>type cast</primary></indexterm>
     For example, you can force a numeric value to be treated as type
     <type>real</> (<type>float4</>) by writing

<programlisting>
REAL '1.23'  -- string style
1.23::REAL   -- PostgreSQL (historical) style
</programlisting>
     </para>
    </sect3>

   <sect3 id="sql-syntax-constants-generic">
    <title>Constants of Other Types</title>

    <indexterm>
     <primary>data type</primary>
     <secondary>constant</secondary>
    </indexterm>

    <para>
     A constant of an <emphasis>arbitrary</emphasis> type can be
     entered using any one of the following notations:
<synopsis>
<replaceable>type</replaceable> '<replaceable>string</replaceable>'
'<replaceable>string</replaceable>'::<replaceable>type</replaceable>
CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
</synopsis>
     The string's text is passed to the input conversion
     routine for the type called <replaceable>type</replaceable>. The
     result is a constant of the indicated type.  The explicit type
     cast may be omitted if there is no ambiguity as to the type the
     constant must be (for example, when it is passed as an argument
     to a non-overloaded function), in which case it is automatically
     coerced.
    </para>

    <para>
     It is also possible to specify a type coercion using a function-like
     syntax:
<synopsis>
<replaceable>typename</replaceable> ( '<replaceable>string</replaceable>' )
</synopsis>
     but not all type names may be used in this way; see <xref
     linkend="sql-syntax-type-casts"> for details.
    </para>

    <para>
     The <literal>::</literal>, <literal>CAST()</literal>, and
     function-call syntaxes can also be used to specify run-time type
     conversions of arbitrary expressions, as discussed in <xref
     linkend="sql-syntax-type-casts">.  But the form
     <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal>
     can only be used to specify the type of a literal constant.
     Another restriction on
     <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal>
     is that it does not work for array types; use <literal>::</literal>
     or <literal>CAST()</literal> to specify the type of an array constant.
    </para>
   </sect3>
  </sect2>

  <sect2 id="sql-syntax-operators">
   <title>Operators</title>

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

   <para>
    An operator name is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
    (63 by default) characters from the following list:
<literallayout>
+ - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ?
</literallayout>

    There are a few restrictions on operator names, however:
    <itemizedlist>
     <listitem>
      <para>
       <literal>--</literal> and <literal>/*</literal> cannot appear
       anywhere in an operator name, since they will be taken as the
       start of a comment.
      </para>
     </listitem>

     <listitem>
      <para>
       A multiple-character operator name cannot end in <literal>+</> or <literal>-</>,
       unless the name also contains at least one of these characters:
<literallayout>
~ ! @ # % ^ &amp; | ` ?
</literallayout>
       For example, <literal>@-</literal> is an allowed operator name,
       but <literal>*-</literal> is not.  This restriction allows
       <productname>PostgreSQL</productname> to parse SQL-compliant
       queries without requiring spaces between tokens.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    When working with non-SQL-standard operator names, you will usually
    need to separate adjacent operators with spaces to avoid ambiguity.
    For example, if you have defined a left unary operator named <literal>@</literal>,
    you cannot write <literal>X*@Y</literal>; you must write
    <literal>X* @Y</literal> to ensure that
    <productname>PostgreSQL</productname> reads it as two operator names
    not one.
   </para>
  </sect2>

  <sect2>
   <title>Special Characters</title>

  <para>
   Some characters that are not alphanumeric have a special meaning
   that is different from being an operator.  Details on the usage can
   be found at the location where the respective syntax element is
   described.  This section only exists to advise the existence and
   summarize the purposes of these characters.

   <itemizedlist>
    <listitem>
     <para>
      A dollar sign (<literal>$</literal>) followed by digits is used
      to represent a positional parameter in the body of a function
      definition or a prepared statement.  In other contexts the
      dollar sign may be part of an identifier.
     </para>
    </listitem>

    <listitem>
     <para>
      Parentheses (<literal>()</literal>) have their usual meaning to
      group expressions and enforce precedence.  In some cases
      parentheses are required as part of the fixed syntax of a
      particular SQL command.
     </para>
    </listitem>

    <listitem>
     <para>
      Brackets (<literal>[]</literal>) are used to select the elements
      of an array.  See <xref linkend="arrays"> for more information
      on arrays.
     </para>
    </listitem>

    <listitem>
     <para>
      Commas (<literal>,</literal>) are used in some syntactical
      constructs to separate the elements of a list.
     </para>
    </listitem>

    <listitem>
     <para>
      The semicolon (<literal>;</literal>) terminates an SQL command.
      It cannot appear anywhere within a command, except within a
      string constant or quoted identifier.
     </para>
    </listitem>

    <listitem>
     <para>
      The colon (<literal>:</literal>) is used to select
      <quote>slices</quote> from arrays. (See <xref
      linkend="arrays">.)  In certain SQL dialects (such as Embedded
      SQL), the colon is used to prefix variable names.
     </para>
    </listitem>

    <listitem>
     <para>
      The asterisk (<literal>*</literal>) has a special meaning when
      used in the <command>SELECT</command> command or with the
      <function>COUNT</function> aggregate function.
     </para>
    </listitem>

    <listitem>
     <para>
      The period (<literal>.</literal>) is used in numeric
      constants, and to separate schema, table, and column names.
     </para>
    </listitem>
   </itemizedlist>

   </para>
  </sect2>

  <sect2 id="sql-syntax-comments">
   <title>Comments</title>

   <indexterm zone="sql-syntax-comments">
    <primary>comment</primary>
    <secondary sortas="SQL">in SQL</secondary>
   </indexterm>

   <para>
    A comment is an arbitrary sequence of characters beginning with
    double dashes and extending to the end of the line, e.g.:
<programlisting>
-- This is a standard SQL comment
</programlisting>
   </para>

   <para>
    Alternatively, C-style block comments can be used:
<programlisting>
/* multiline comment
 * with nesting: /* nested block comment */
 */
</programlisting>
    where the comment begins with <literal>/*</literal> and extends to
    the matching occurrence of <literal>*/</literal>. These block
    comments nest, as specified in the SQL standard but unlike C, so that one can
    comment out larger blocks of code that may contain existing block
    comments.
   </para>

   <para>
    A comment is removed from the input stream before further syntax
    analysis and is effectively replaced by whitespace.
   </para>
  </sect2>

  <sect2 id="sql-precedence">
   <title>Lexical Precedence</title>

   <indexterm zone="sql-precedence">
    <primary>operator</primary>
    <secondary>precedence</secondary>
   </indexterm>

   <para>
    <xref linkend="sql-precedence-table"> shows the precedence and
    associativity of the operators in <productname>PostgreSQL</>.
    Most operators have the same precedence and are left-associative.
    The precedence and associativity of the operators is hard-wired
    into the parser.  This may lead to non-intuitive behavior; for
    example the Boolean operators <literal>&lt;</> and
    <literal>&gt;</> have a different precedence than the Boolean
    operators <literal>&lt;=</> and <literal>&gt;=</>.  Also, you will
    sometimes need to add parentheses when using combinations of
    binary and unary operators.  For instance
<programlisting>
SELECT 5 ! - 6;
</programlisting>
   will be parsed as
<programlisting>
SELECT 5 ! (- 6);
</programlisting>
    because the parser has no idea -- until it is too late -- that
    <token>!</token> is defined as a postfix operator, not an infix one.
    To get the desired behavior in this case, you must write
<programlisting>
SELECT (5 !) - 6;
</programlisting>
    This is the price one pays for extensibility.
   </para>

   <table id="sql-precedence-table">
    <title>Operator Precedence (decreasing)</title>

    <tgroup cols="3">
     <thead>
      <row>
       <entry>Operator/Element</entry>
       <entry>Associativity</entry>
       <entry>Description</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><token>.</token></entry>
       <entry>left</entry>
       <entry>table/column name separator</entry>
      </row>

      <row>
       <entry><token>::</token></entry>
       <entry>left</entry>
       <entry><productname>PostgreSQL</productname>-style typecast</entry>
      </row>

      <row>
       <entry><token>[</token> <token>]</token></entry>
       <entry>left</entry>
       <entry>array element selection</entry>
      </row>

      <row>
       <entry><token>-</token></entry>
       <entry>right</entry>
       <entry>unary minus</entry>
      </row>

      <row>
       <entry><token>^</token></entry>
       <entry>left</entry>
       <entry>exponentiation</entry>
      </row>

      <row>
       <entry><token>*</token> <token>/</token> <token>%</token></entry>
       <entry>left</entry>
       <entry>multiplication, division, modulo</entry>
      </row>

      <row>
       <entry><token>+</token> <token>-</token></entry>
       <entry>left</entry>
       <entry>addition, subtraction</entry>
      </row>

      <row>
       <entry><token>IS</token></entry>
       <entry></entry>
       <entry><literal>IS TRUE</>, <literal>IS FALSE</>, <literal>IS UNKNOWN</>, <literal>IS NULL</></entry>
      </row>

      <row>
       <entry><token>ISNULL</token></entry>
       <entry></entry>
       <entry>test for null</entry>
      </row>

      <row>
       <entry><token>NOTNULL</token></entry>
       <entry></entry>
       <entry>test for not null</entry>
      </row>

      <row>
       <entry>(any other)</entry>
       <entry>left</entry>
       <entry>all other native and user-defined operators</entry>
      </row>

      <row>
       <entry><token>IN</token></entry>
       <entry></entry>
       <entry>set membership</entry>
      </row>

      <row>
       <entry><token>BETWEEN</token></entry>
       <entry></entry>
       <entry>containment</entry>
      </row>

      <row>
       <entry><token>OVERLAPS</token></entry>
       <entry></entry>
       <entry>time interval overlap</entry>
      </row>

      <row>
       <entry><token>LIKE</token> <token>ILIKE</token> <token>SIMILAR</token></entry>
       <entry></entry>
       <entry>string pattern matching</entry>
      </row>