path: root/doc/src/sgml/syntax.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.29 2000/12/18 23:39:37 tgl Exp $
-->

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

  <abstract>
   <para>
    A description of the general syntax of SQL.
   </para>
  </abstract>

  <para>
   <acronym>SQL</acronym> manipulates sets of data. The language is
   composed of various <firstterm>key words</firstterm>. Arithmetic
   and procedural expressions are allowed. We will cover these topics
   in this chapter; subsequent chapters will include details on data
   types, functions, and operators.
  </para>

  <sect1 id="sql-keywords">
   <title>Key Words</title>

   <para>
    <acronym>SQL92</acronym> defines <firstterm>key words</firstterm> 
    for the language
    which have specific meaning. Some key words are
    <firstterm>reserved</firstterm>, which indicates that they are
    restricted to appear in only certain contexts. Other key words are
    <firstterm>not restricted</firstterm>, which indicates that in certain 
    contexts they
    have a specific meaning but are not otherwise constrained.
   </para>

   <para>
    <productname>Postgres</productname> implements an extended subset of the
    <acronym>SQL92</acronym> and <acronym>SQL3</acronym> languages. Some language
    elements are not as restricted in this implementation as is
    called for in the language standards, in part due
    to the extensibility features of <productname>Postgres</productname>. 
   </para>

   <para>
    Information on <acronym>SQL92</acronym> and <acronym>SQL3</acronym> key words
    is derived from <xref linkend="DATE97" endterm="DATE97">.
   </para>

   <sect2>
    <title>Reserved Key Words</title>

    <para>
     <acronym>SQL92</acronym> and <acronym>SQL3</acronym> have 
     <firstterm>reserved key words</firstterm> which are not allowed 
     as identifiers and not allowed in any usage other than as fundamental
     tokens in <acronym>SQL</acronym> statements.
     <productname>Postgres</productname> has additional key words
     which have similar restrictions. In particular, these key words
     are not allowed as column or table names, though in some cases
     they are allowed to be column labels (i.e. in AS clauses).
    </para>

    <tip>
     <para>
      Any string can be used as an identifier if surrounded by
      double quotes (<quote>like this!</quote>). Some care is required since
      such an identifier will be case sensitive
      and will retain embedded whitespace and most other special characters.
     </para>
    </tip>

    <para>
     The following are <productname>Postgres</productname>
     reserved words which are neither <acronym>SQL92</acronym>
     nor <acronym>SQL3</acronym> reserved words. These are allowed
     to be present as column labels, but not as identifiers:

     <programlisting>
ABORT ANALYZE 
BINARY 
CLUSTER CONSTRAINT COPY
DO 
EXPLAIN EXTEND
LISTEN LOAD LOCK 
MOVE 
NEW NONE NOTIFY 
OFFSET 
RESET 
SETOF SHOW
UNLISTEN UNTIL 
VACUUM VERBOSE
     </programlisting>
    </para>

    <para>
     The following are <productname>Postgres</productname>
     reserved words which are also <acronym>SQL92</acronym> 
     or <acronym>SQL3</acronym> reserved words, and which
     are allowed to be present as column labels, but not as identifiers:

     <programlisting>
ALL ANY ASC BETWEEN BIT BOTH 
CASE CAST CHAR CHARACTER CHECK COALESCE COLLATE COLUMN
 CONSTRAINT CROSS CURRENT CURRENT_DATE CURRENT_TIME
 CURRENT_TIMESTAMP CURRENT_USER 
DEC DECIMAL DEFAULT DESC DISTINCT 
ELSE END EXCEPT EXISTS EXTRACT 
FALSE FLOAT FOR FOREIGN FROM FULL 
GLOBAL GROUP 
HAVING 
IN INNER INTERSECT INTO IS 
JOIN 
LEADING LEFT LIKE LOCAL
NATURAL NCHAR NOT NULL NULLIF NUMERIC 
ON OR ORDER OUTER OVERLAPS 
POSITION PRECISION PRIMARY PUBLIC
REFERENCES RIGHT 
SELECT SESSION_USER SOME SUBSTRING 
TABLE THEN TO TRANSACTION TRIM TRUE
UNION UNIQUE USER 
VARCHAR 
WHEN WHERE 
     </programlisting>

     The following are <productname>Postgres</productname>
     reserved words which are also <acronym>SQL92</acronym> 
     or <acronym>SQL3</acronym> reserved words:

     <programlisting>
ADD ALTER AND AS 
BEGIN BY
CASCADE CLOSE COMMIT CREATE CURSOR
DECLARE DEFAULT DELETE DESC DISTINCT DROP
EXECUTE EXISTS EXTRACT
FETCH FLOAT FOR FROM FULL
GRANT 
HAVING
IN INNER INSERT INTERVAL INTO INOUT IS
JOIN 
LEADING LEFT LIKE LOCAL
NAMES NATIONAL NATURAL NCHAR NO NOT NULL 
ON OR OUT OUTER
PARTIAL PRIMARY PRIVILEGES PROCEDURE PUBLIC
REFERENCES REVOKE RIGHT ROLLBACK
SELECT SESSION SET SUBSTRING
TO TRAILING TRIM 
UNION UNIQUE UPDATE USING
VALUES VARCHAR VARYING VIEW
WHERE WITH WITHOUT WORK 
     </programlisting>
    </para>

    <para>
     The following are <acronym>SQL92</acronym> reserved key words which
     are not <productname>Postgres</productname> reserved key words, but which
     if used as function names are always translated into the function
     <function>CHAR_LENGTH</function>:

     <programlisting>
CHARACTER_LENGTH
     </programlisting>
    </para>

    <para>
     The following are <acronym>SQL92</acronym> or <acronym>SQL3</acronym> 
     reserved key words which
     are not <productname>Postgres</productname> reserved key words, but
     if used as type names are always translated into an alternate, native type:

     <programlisting>
BOOLEAN DOUBLE FLOAT INT INTEGER INTERVAL REAL SMALLINT
     </programlisting>
    </para>

    <para>
     The following are not keywords of any kind, but when used in the
     context of a type name are translated into a native
     <productname>Postgres</productname> type, and when used in the
     context of a function name are translated into a native function:

     <programlisting>
DATETIME TIMESPAN
     </programlisting>

     (translated to <type>TIMESTAMP</type> and <type>INTERVAL</type>,
     respectively). This feature is intended to help with
     transitioning to v7.0, and will be removed in the next full
     release (likely v7.1).
    </para>

    <para>
     The following are either <acronym>SQL92</acronym>
     or <acronym>SQL3</acronym> reserved key words
     which are not key words in <productname>Postgres</productname>.
     These have no proscribed usage in <productname>Postgres</productname>
     at the time of writing (v7.0) but may become reserved key words in the
     future:

     <note>
      <para>
       Some of these key words represent functions in <acronym>SQL92</acronym>.
       These functions are defined in <productname>Postgres</productname>,
       but the parser does not consider the names to be key words and they are allowed
       in other contexts.
      </para>
     </note>

     <programlisting>
ALLOCATE ARE ASSERTION AT AUTHORIZATION AVG 
BIT_LENGTH
CASCADED CATALOG CHAR_LENGTH CHARACTER_LENGTH COLLATION
 CONNECT CONNECTION CONTINUE CONVERT CORRESPONDING COUNT
 CURRENT_SESSION
DATE DEALLOCATE DEC DESCRIBE DESCRIPTOR
 DIAGNOSTICS DISCONNECT DOMAIN 
ESCAPE EXCEPT EXCEPTION EXEC EXTERNAL 
FIRST FOUND
GET GO GOTO 
IDENTITY INDICATOR INPUT INTERSECT
LAST LOWER 
MAX MIN MODULE 
OCTET_LENGTH OPEN OUTPUT OVERLAPS 
PREPARE PRESERVE 
ROWS
SCHEMA SECTION SESSION SIZE SOME 
 SQL SQLCODE SQLERROR SQLSTATE SUM SYSTEM_USER
TEMPORARY TRANSLATE TRANSLATION 
UNKNOWN UPPER USAGE
VALUE 
WHENEVER WRITE
     </programlisting>
    </para>
   </sect2>

   <sect2>
    <title>Non-reserved Keywords</title>

    <para>
     <acronym>SQL92</acronym> and <acronym>SQL3</acronym> have 
     <firstterm>non-reserved keywords</firstterm> which have
     a prescribed meaning in the language but which are also allowed
     as identifiers.
     <productname>Postgres</productname> has additional keywords
     which allow similar unrestricted usage.
     In particular, these keywords
     are allowed as column or table names.
    </para>

    <para>
     The following are <productname>Postgres</productname>
     non-reserved key words which are neither <acronym>SQL92</acronym>
     nor <acronym>SQL3</acronym> non-reserved key words:

     <programlisting>
ACCESS AFTER AGGREGATE 
BACKWARD BEFORE 
CACHE COMMENT CREATEDB CREATEUSER CYCLE
DATABASE DELIMITERS 
EACH ENCODING EXCLUSIVE
FORCE FORWARD FUNCTION 
HANDLER
INCREMENT INDEX INHERITS INSENSITIVE INSTEAD ISNULL
LANCOMPILER LOCATION 
MAXVALUE MINVALUE MODE
NOCREATEDB NOCREATEUSER NOTHING NOTIFY NOTNULL 
OIDS OPERATOR 
PASSWORD PROCEDURAL
RECIPE REINDEX RENAME RETURNS ROW RULE
SEQUENCE SERIAL SHARE START STATEMENT STDIN STDOUT 
TEMP TRUSTED 
UNLISTEN UNTIL 
VALID VERSION
     </programlisting>
    </para>

    <para>
     The following are <productname>Postgres</productname>
     non-reserved key words which are <acronym>SQL92</acronym>
     or <acronym>SQL3</acronym> reserved key words:

     <programlisting>
ABSOLUTE ACTION
CHARACTERISTICS CONSTRAINTS
DAY DEFERRABLE DEFERRED 
HOUR
IMMEDIATE INITIALLY INSENSITIVE ISOLATION 
KEY 
LANGUAGE LEVEL 
MATCH MINUTE MONTH
NEXT 
OF ONLY OPTION 
PATH PENDANT PRIOR PRIVILEGES 
READ RELATIVE RESTRICT 
SCHEMA SCROLL SECOND
TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TRIGGER 
YEAR
ZONE
     </programlisting>
    </para>

    <para>
     The following are <productname>Postgres</productname>
     non-reserved key words which are also either <acronym>SQL92</acronym>
     or <acronym>SQL3</acronym> non-reserved key words:

     <programlisting>
COMMITTED SERIALIZABLE TYPE
     </programlisting>
    </para>

    <para>
     The following are either <acronym>SQL92</acronym>
     or <acronym>SQL3</acronym> non-reserved key words which are not
     key words of any kind in <productname>Postgres</productname>:

     <programlisting>
ADA
C CATALOG_NAME CHARACTER_SET_CATALOG CHARACTER_SET_NAME 
 CHARACTER_SET_SCHEMA CLASS_ORIGIN COBOL COLLATION_CATALOG
 COLLATION_NAME COLLATION_SCHEMA COLUMN_NAME
 COMMAND_FUNCTION CONDITION_NUMBER
 CONNECTION_NAME CONSTRAINT_CATALOG CONSTRAINT_NAME
 CONSTRAINT_SCHEMA CURSOR_NAME
DATA DATE_TIME_INTERVAL_CODE DATE_TIME_INTERVAL_PRECISION
 DYNAMIC_FUNCTION
FORTRAN
LENGTH
MESSAGE_LENGTH MESSAGE_OCTET_LENGTH MORE MUMPS
NAME NULLABLE NUMBER
PAD PASCAL PLI
REPEATABLE RETURNED_LENGTH RETURNED_OCTET_LENGTH
 RETURNED_SQLSTATE ROW_COUNT
SCALE SCHEMA_NAME SERVER_NAME SPACE SUBCLASS_ORIGIN
TABLE_NAME
UNCOMMITTED UNNAMED
     </programlisting>
    </para>
   </sect2>
  </sect1>

  <sect1 id="sql-comments">
   <title>Comments</title>

   <para>
    A <firstterm>comment</firstterm>
    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>
    We also support C-style block comments, e.g.:

    <programlisting>
/* multi-line 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 SQL99, so that one can comment out
    larger blocks of code which may contain existing block comments.
   </para>
  </sect1>

  <sect1 id="sql-names">
   <title>Names</title>

   <para>
    Names in SQL must begin with a letter
    (<literal>a</literal>-<literal>z</literal>) or underscore
    (<literal>_</literal>).
    Subsequent characters in a name can be letters, digits
    (<literal>0</literal>-<literal>9</literal>),
    or underscores.  The system uses no more than NAMEDATALEN-1 characters
    of a name; longer names can be written in queries, but they will be
    truncated.
    By default, NAMEDATALEN is 32 so the maximum name length is 31 (but
    at the time the system is built, NAMEDATALEN can be changed in
    <filename>src/include/postgres_ext.h</filename>).
   </para>

   <para>
    Names containing other characters may be formed by surrounding them
    with double quotes (<literal>"</literal>).  For example, table or column
    names may contain
    otherwise disallowed characters such as spaces, ampersands, etc. if
    quoted.  Quoting a name also makes it case-sensitive,
    whereas unquoted names are always folded to lower case.  For example,
    the names <literal>FOO</literal>, <literal>foo</literal>
    and <literal>"foo"</literal> are
    considered the same by <productname>Postgres</productname>, but
    <literal>"Foo"</literal> is a different name.
   </para>

   <para>
    Double quotes can also be used to protect a name that would otherwise
    be taken to be an SQL keyword.  For example, <literal>IN</literal>
    is a keyword but <literal>"IN"</literal> is a name.
   </para>
  </sect1>

  <sect1 id="sql-constants">
   <title>Constants</title>

   <para>
    There are three kinds of <firstterm>implicitly typed constants</firstterm>
    in <productname>Postgres</productname>: strings, integers, 
    and floating point numbers.  Constants can
    also be specified with explicit types, which can enable more
    accurate representation and more efficient handling by the
    backend. The implicit constants are described below; explicit
    constants are discussed afterwards.
   </para>

   <sect2>
    <title>String Constants</title>

    <para>
     <firstterm>Strings</firstterm>
     in SQL are arbitrary sequences of ASCII characters bounded by single
     quotes ("'", e.g. <literal>'This is a string'</literal>).
     SQL92 allows single quotes to be embedded in strings by typing two 
     adjacent single quotes (e.g. <literal>'Dianne''s horse'</literal>).
     In <productname>Postgres</productname> single quotes may alternatively
     be escaped with a backslash ("\", e.g.
     <literal>'Dianne\'s horse'</literal>).  To include a
     backslash in a string constant, type two backslashes.
     Non-printing characters may also be embedded within strings by
     prepending them with a backslash
     (e.g. <literal>'\<replaceable>tab</replaceable>'</literal>).
    </para>

   </sect2>

   <sect2>
    <title>Integer Constants</title>

    <para>
     <firstterm>Integer constants</firstterm>
     in SQL are sequences of ASCII digits with no decimal point.
     The range of legal values depends on which integer datatype is
     used, but the plain <literal>integer</literal> type accepts values
     ranging from -2147483648 to +2147483647.
    </para>
   </sect2>

   <sect2>
    <title>Floating Point Constants</title>

    <para>
     <firstterm>Floating point constants</firstterm>
     consist of an integer part, a decimal point, and a fraction part or
     scientific notation of the following format:

     <synopsis>
{<replaceable>dig</replaceable>}.{<replaceable>dig</replaceable>} [e [+-] {<replaceable>dig</replaceable>}]
     </synopsis>

     where <replaceable>dig</replaceable> is one or more digits.
     You must include at least one <replaceable>dig</replaceable> after the
     period and after the [+-] if you use those options.  An exponent with
     a missing mantissa has a mantissa of 1 inserted.  There may be no
     extra characters embedded in the string.
    </para>

    <para>
     Floating point constaints are of type
     <type>float8</type>. <type>float4</type> can be specified
     explicitly by using <acronym>SQL92</acronym> string notation or
     <productname>Postgres</productname> type notation:

     <programlisting>
float4 '1.23'  -- string style
'1.23'::float4 -- Postgres (historical) style
     </programlisting>
    </para>
   </sect2>

   <sect2>
    <title>Constants of Postgres User-Defined Types</title>

    <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 value inside the string 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 typecast may be omitted
     if there is no ambiguity as to the type the constant must be, 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>value</replaceable> )
     </synopsis>

     although this only works for types whose names are also valid as
     function names.  (For example, <literal>double precision</literal>
     can't be used this way --- but the equivalent <literal>float8</literal>
     can.)
    </para>

    <para>
     The <literal>::</literal>, <literal>CAST()</literal>, and function-call
     syntaxes can also be used to specify run-time type conversions.  But
     the form <replaceable>type</replaceable>
     '<replaceable>string</replaceable>' can only be used to specify the
     type of a literal constant.
    </para>
   </sect2>

   <sect2>
    <title>Array constants</title>

    <para>
     <firstterm>Array constants</firstterm>
     are n-dimensional arrays of any Postgres datatype.
     The general format of an array constant is the following:

     <synopsis>
{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }
     </synopsis>

     where <replaceable>delim</replaceable>
     is the delimiter character for the type, as recorded in its
     <literal>pg_type</literal> class entry.
     (For all built-in types, this is the comma character ",".)
     Each <replaceable>val</replaceable> is either a constant
     of the array element type, or a sub-array.
     An example of an array constant is

     <programlisting>
{{1,2,3},{4,5,6},{7,8,9}}
     </programlisting>

     This constant is a two-dimensional, 3 by 3 array consisting of three
     sub-arrays of integers.
    </para>

    <para>
     Individual array elements can be placed between double-quote
     marks (<literal>"</literal>) to avoid ambiguity problems with respect to
     white space.
     Without quote marks, the array-value parser will skip leading white space.
    </para>
   </sect2>
  </sect1>

  <sect1 id="sql-columns">
   <title>Fields and Columns</title>

   <sect2>
    <title>Fields</title>

    <para>
     A <firstterm>field</firstterm>
     is either a user-defined attribute of a given class or one of the
     following system-defined attributes:

     <variablelist>
      <varlistentry>
       <term>oid</term>
       <listitem>
	<para>
	 stands for the unique identifier of an instance which is added by
	 Postgres to all instances automatically. OIDs are not reused and are
	 32-bit quantities.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>xmin</term>
       <listitem>
	<para>
	 The identity of the inserting transaction.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
      <term>xmax</term>
       <listitem>
	<para>
	 The identity of the deleting transaction.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
      <term>cmin</term>
       <listitem>
	<para>
	 The command identifier within the inserting transaction.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
      <term>cmax</term>
       <listitem>
	<para>
	 The command identifier within the deleting transaction.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>

    <para>
     For further information on the system attributes consult
     <xref linkend="STON87a" endterm="STON87a">.
     Transaction and command identifiers are 32 bit quantities.
    </para>
   </sect2>

   <sect2>
    <title>Columns</title>

    <para>
     A <firstterm>column</firstterm> is a construct of the form:

     <synopsis>
<replaceable>instance</replaceable>{.<replaceable>composite_field</replaceable>}.<replaceable>field</replaceable> `['<replaceable>subscript</replaceable>`]'
     </synopsis>

     <replaceable>instance</replaceable>
     identifies a particular class and can be thought of as standing for
     the instances of that class.  An instance variable is either a class
     name, an alias for a class defined by means of a FROM clause,
     or the keyword NEW or OLD.
     (NEW and OLD can only appear in the action portion of a rule, while
     other instance variables can be used in any SQL statement.)  The
     instance name can be omitted if the first field name is unique
     across all the classes being used in the current query.
     <replaceable>composite_field</replaceable>
     is a field of of one of the Postgres composite types,
     while successive composite fields select attributes in the
     class(s) to which the composite field evaluates.  Lastly,
     <replaceable>field</replaceable>
     is a normal (base type) field in the class(s) last addressed.  If
     <replaceable>field</replaceable>
     is of an array type,
     then the optional <replaceable>subscript</replaceable>
     selects a specific element in the array.  If no subscript is
     provided, then the whole array is selected.
    </para>
   </sect2>
  </sect1>

  <sect1 id="sql-operators">
   <title>Operators</title>

   <para>
    Any built-in or user-defined operator may be used in SQL.
    For the list of built-in operators consult <xref linkend="functions">.
    For a list of user-defined operators consult your system administrator
    or run a query on the <literal>pg_operator</literal> class.
    Parentheses may be used for arbitrary grouping of operators in expressions.
   </para>
  </sect1>

  <sect1 id="sql-expressions">
   <title>Expressions</title>

   <para>
    <acronym>SQL92</acronym> allows <firstterm>expressions</firstterm>
    to transform data in tables. Expressions may contain operators
    and functions.
   </para>

   <para>
    An expression is one of the following:

    <simplelist>
     <member>constant</member>
     <member>column</member>
     <member><replaceable>expression</replaceable> <replaceable>binary_operator</replaceable> <replaceable>expression</replaceable></member>
     <member><replaceable>expression</replaceable> <replaceable>right_unary_operator</replaceable></member>
     <member><replaceable>left_unary_operator</replaceable> <replaceable>expression</replaceable></member>
     <member>( <replaceable>expression</replaceable> )</member>
     <member>parameter</member>
     <member>functional expression</member>
     <member>aggregate expression</member>
    </simplelist>
   </para>

   <para>
    We have already discussed constants and columns.  The three kinds of
    operator expressions indicate respectively binary (infix), right-unary
    (suffix) and left-unary (prefix) operators.  The following sections
    discuss the remaining options.
   </para>

   <sect2>
    <title>Parameters</title>

    <para>
     A <firstterm>parameter</firstterm>
     is used to indicate a parameter in a SQL function.  Typically this
     is used in SQL function definition statements.  The form of a
     parameter is:

     <synopsis>
$<replaceable class="parameter">number</replaceable>
     </synopsis>
    </para>

    <para>
     For example, consider the definition of a function,
     <function>dept</function>, as

     <programlisting>
CREATE FUNCTION dept (name)
	RETURNS dept
	AS 'select * from dept where name = $1'
	LANGUAGE 'sql';
     </programlisting>
    </para>
   </sect2>

   <sect2>
    <title>Functional Expressions</title>

    <para>
     A <firstterm>functional expression</firstterm>
     is the name of a legal SQL function, followed by its argument list
     enclosed in parentheses:

     <synopsis>
<replaceable>function</replaceable> (<replaceable>expression</replaceable> [, <replaceable>expression</replaceable> ... ] )
     </synopsis>
    </para>

    <para>
     For example, the following computes the square root of an employee
     salary:

     <programlisting>
sqrt(emp.salary)
     </programlisting>
    </para>
   </sect2>

   <sect2 id="syntax-aggregates">
    <title>Aggregate Expressions</title>

    <para>
     An <firstterm>aggregate expression</firstterm> represents the application
     of an aggregate function across the rows selected by a query.
     An aggregate function reduces multiple inputs to a single output value,
     such as the sum or average of the inputs.
     The syntax of an aggregate expression is one of the following:

    <simplelist>
     <member><replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>)</member>
     <member><replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>)</member>
     <member><replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)</member>
     <member><replaceable>aggregate_name</replaceable> ( * )</member>
    </simplelist>

     where <replaceable>aggregate_name</replaceable> is a previously defined
     aggregate, and <replaceable>expression</replaceable> is any expression
     that doesn't itself contain an aggregate expression.
    </para>

    <para>
     The first form of aggregate expression invokes the aggregate across all
     input rows for which the given expression yields a non-null value.
     The second form is the same as the first, since ALL is the default.
     The third form invokes the aggregate for all distinct non-null values
     of the expression found in the input rows.  The last form invokes the
     aggregate once for each input row regardless of null or non-null values;
     since no particular input value is specified, it is generally only useful
     for the count() aggregate.
    </para>

    <para>
      For example, count(*) yields the total number of input rows;
      count(f1) yields the number of input rows in which f1 is non-null;
      count(distinct f1) yields the number of distinct non-null values of f1.
    </para>
   </sect2>

   <sect2>
    <title>Target List</title>

    <para>
     A <firstterm>target list</firstterm>
     is a comma-separated list of one or more elements, each
     of which must be of the form:

     <synopsis>
<replaceable>expression</replaceable> [ AS <replaceable>result_attname</replaceable> ]
     </synopsis>

     where <replaceable>result_attname</replaceable>
     is the name to be assigned to the created column.  If 
     <replaceable>result_attname</replaceable>
     is not present, then <productname>Postgres</productname> selects a
     default name based on the contents of <replaceable>expression</replaceable>.
     If <replaceable>expression</replaceable> is a simple attribute reference
     then the default name will be the same as that attribute's name, but
     otherwise the implementation is free to assign any default name.
    </para>
   </sect2>

   <sect2>
    <title>Qualification</title>

    <para>
     A <firstterm>qualification</firstterm>
     consists of any number of clauses connected by the logical operators:

     <simplelist>
      <member>NOT</member>
      <member>AND</member>
      <member>OR</member>
     </simplelist>

     A clause is an <replaceable>expression</replaceable>
     that evaluates to a <literal>boolean</literal> over a set of instances.
    </para>
   </sect2>

   <sect2>
    <title>From List</title>

    <para>
     The <firstterm>from list</firstterm>
     is a comma-separated list of <firstterm>from-expressions</firstterm>.
     The simplest possibility for a from-expression is:

     <synopsis>
<replaceable>class_reference</replaceable> [ [ AS ] <replaceable class="PARAMETER">alias</replaceable> ]
     </synopsis>

     where <replaceable>class_reference</replaceable> is of the form

     <synopsis>
[ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ]
     </synopsis>

     The from-expression defines an instance variable that ranges over the
     rows of the specified table.  The instance variable's name is either
     the table name, or the <replaceable>alias</replaceable> if one is given.
     Ordinarily, if the table has child tables then the instance variable
     will range over all rows in the inheritance hierarchy starting with
     the specified table.  If <literal>ONLY</literal> is specified then
     child tables are not included.  A trailing asterisk <literal>*</literal>
     can be written to specifically indicate that child tables are included
     (<literal>ONLY</literal> and <literal>*</literal> are mutually
     exclusive).
    </para>

    <para>
     A from-expression can also be a sub-query:

     <synopsis>
( <replaceable class="PARAMETER">select-statement</replaceable> ) [ AS ] <replaceable class="PARAMETER">alias</replaceable>
     </synopsis>

     Here, the effect is as though the SELECT were executed and its results
     stored in a temporary table, which then becomes available as an instance
     variable under the given <replaceable>alias</replaceable>.
    </para>

    <para>
     Finally, a from-expression can be built up from simpler from-expressions
     using JOIN clauses:

     <synopsis>
<replaceable class="PARAMETER">from_expression</replaceable> [ NATURAL ] <replaceable class="PARAMETER">join_type</replaceable> <replaceable class="PARAMETER">from_expression</replaceable>
    [ ON <replaceable class="PARAMETER">join_condition</replaceable> | USING ( <replaceable class="PARAMETER">join_column_list</replaceable> ) ]
     </synopsis>

     This syntax allows specification of <firstterm>outer joins</firstterm>.
     For details see the reference page for SELECT.
    </para>
   </sect2>


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

   <para>
    The precedence and associativity of the operators is hard-wired
    into the parser.  Most operators have the same precedence and are
    left-associative.  This may lead to non-intuitive behavior; for
    example the boolean operators "&lt;" and "&gt;" have a different
    precedence than the boolean operators "&lt;=" and "&gt;=".  Also,
    you will sometimes need to add parentheses when using combinations
    of binary and unary operators.  For instance
<programlisting>
SELECT 5 &amp; ~ 6;
</programlisting>
   will be parsed as
<programlisting>
SELECT (5 &amp;) ~ 6;
</programlisting>
    because the parser has no idea that <token>&amp;</token> is
    defined as a binary operator.  This is the price one pays for
    extensibility.
   </para>

   <table tocentry="1">
    <title>Operator Ordering (decreasing precedence)</title>

    <tgroup cols="2">
     <thead>
      <row>
       <entry>OperatorElement</entry>
       <entry>Associativity</entry>
       <entry>Description</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><token>::</token></entry>
       <entry>left</entry>
       <entry><productname>Postgres</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>left</entry>
       <entry>table/column name separator</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>test for TRUE, FALSE, 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></entry>
       <entry></entry>
       <entry>string pattern matching</entry>
      </row>

      <row>
       <entry><token>&lt;</token> <token>&gt;</token></entry>
       <entry></entry>
       <entry>less than, greater than</entry>
      </row>

      <row>
       <entry><token>=</token></entry>
       <entry>right</entry>
       <entry>equality, assignment</entry>
      </row>

      <row>
       <entry><token>NOT</token></entry>
       <entry>right</entry>
       <entry>logical negation</entry>
      </row>

      <row>
       <entry><token>AND</token></entry>
       <entry>left</entry>
       <entry>logical conjunction</entry>
      </row>

      <row>
       <entry><token>OR</token></entry>
       <entry>left</entry>
       <entry>logical disjunction</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Note that the operator precedence rules also apply to user-defined
    operators that have the same names as the built-in operators
    mentioned above.  For example, if you define a
    <quote>+</quote> operator for some custom data type it will have
    the same precedence as the built-in <quote>+</quote> operator, no
    matter what yours does.
   </para>
  </sect2>

  </sect1>
 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->