INTRODUCTION(SQL)           PostgreSQL          INTRODUCTION(SQL)

Section 4 - SQL Commands (COMMANDS)
General Information
DESCRIPTION
       The  following  is  a description of the general syntax of
       SQL.  Individual SQL statements and commands  are  treated
       separately  in  the  document;  this section describes the
       syntactic classes from which the constituent parts of  SQL
       statements are drawn.

Comments
       A comment is an arbitrary sequence of characters following
       double dashes up to the end of the line.  We also  support
       double-slashes as comments, e.g.:
       -- This is a standard SQL comment
       // And this is another supported comment style, like C++

       We also support C-style comments, e.g.:
       /* multi
          line
          comment */

Names
       Names  in  SQL  are  sequences  of  less  than NAMEDATALEN
       alphanumeric characters, starting with an alphabetic char-
       acter.   By  default, NAMEDATALEN is set to 32, but at the
       time the system is built, NAMEDATALEN can  be  changed  by
       changing  the  #ifdef  in  src/backend/include/postgres.h.
       Underscore ("_") is considered an alphabetic character.

Keywords
       The following identifiers are reserved for use as keywords
       and may not be used otherwise:

       In  addition, all Postgres classes have several predefined
       attributes used by the system.

Constants
       There are six types of constants for use in SQL.  They are
       described below.

String Constants
       Strings in SQL are arbitrary sequences of ASCII characters
       bounded by single  quotes  ('  ').  Uppercase  alphabetics
       within strings are accepted literally.  Non-printing char-
       acters may be embedded within strings by  prepending  them
       with  a  backslash,  e.g.,  `\n'.  Also, in order to embed
       quotes within strings, it is necessary to prefix them with
       `\' .  The same convention applies to `\' itself.  Because
       of the limitations on instance sizes, string constants are
       currently  limited  to a length of a little less than 8192
       bytes.  Larger objects may be created using  the  Postgres
       Large Object interface.

Integer Constants
       Integer  constants  in  SQL are collection of ASCII digits
       with  no  decimal  point.    Legal   values   range   from
       -2147483647  to  +2147483647.  This will vary depending on
       the operating system and host machine.

Floating Point Constants
       Floating point constants consist of  an  integer  part,  a
       decimal  point, and a fraction part or scientific notation
       of the following format:
       {<dig>} .{<dig>} [e [+-] {<dig>}]
       Where <dig> is a digit.  You must  include  at  least  one
       <dig> after the period and after the [+-] if you use those
       options.  An exponent with a missing mantissa has  a  man-
       tissa  of  1  inserted.   There may be no extra characters
       embedded in the string.  Floating point constaints are  of
       type float4.

Constants of Postgres User-Defined Types
       A  constant  of an arbitrary type can be entered using the
       notation:
       or
       CAST 'string' AS type-name
       The value inside the string is passed to the input conver-
       sion  routine for the type called type-name. 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.

Array constants
       Array constants are arrays of any Postgres type, including
       other arrays, string constants, etc.  The  general  format
       of an array constant is the following:
       {<val1><delim><val2><delim>}
       Where  <delim> is the delimiter for the type stored in the
       "pg_type" class.  (For built-in types, this is  the  comma
       character, ",".)  An example of an array constant is
       {{1,2,3},{4,5,6},{7,8,9}}
       This  constant is a two-dimensional, 3 by 3 array consist-
       ing of three sub-arrays of integers.

       Individual array elements can and should be placed between
       quotation marks whenever possible to avoid ambiguity prob-
       lems with respect to leading white space.

FIELDS AND COLUMNS
Fields
       A field is either an attribute of a given class or one  of
       the following:
       oid
       xmin
       xmax
       cmin
       cmax

       Oid  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.

       Xmin, cmin, xmax and cmax stand respectively for the iden-
       tity of the inserting transaction, the command  identifier
       within  the  transaction,  the  identity  of  the deleting
       transaction and its associated deleting command.  For fur-
       ther  information on these fields consult [STON87].  Times
       are represented internally as instances of  the  "abstime"
       data type.  Transaction and command identifiers are 32 bit
       quantities.  Transactions are assigned sequentially start-
       ing at 512.

Columns
       A column is a construct of the form:
       Instance-variable{.composite_field}.field `['number`]'
       Instance-variable 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, a surrogate
       for a class defined by means of a from clause, or the key-
       word  new  or current.  New and current can only appear in
       the action portion of a rule, while other  instance  vari-
       ables  can  be used in any SQL statement.  Composite_field
       is a field of of one of the Postgres composite types indi-
       cated  in  the pgbuiltin(l) section, while successive com-
       posite fields address attributes in the class(s) to  which
       the  composite field evaluates.  Lastly, field is a normal
       (base type) field in  the  class(s)  last  addressed.   If
       field  is of type array, then the optional number designa-
       tor indicates a specific element in the array.  If no num-
       ber is indicated, then all array elements are returned.

Operators
       Any  built-in system, or user-defined operator may be used
       in SQL.  For the list of  built-in  and  system  operators
       consult  pgbuiltin(3).   For a list of user-defined opera-
       tors consult your system administrator or run a  query  on
       the  pg_operator class.  Parentheses may be used for arbi-
       trary grouping of operators.

Expressions (a_expr)
       An expression is one of the following:
       ( a_expr )
       constant
       attribute
       a_expr binary_operator a_expr
       a_expr right_unary_operator
       left_unary_operator a_expr
       parameter
       functional expressions
       aggregate expressions
       We have already discussed constants and  attributes.   The
       two  kinds  of  operator expressions indicate respectively
       binary and left_unary expressions.  The following sections
       discuss the remaining options.

Parameters
       A parameter is used to indicate a parameter in a SQL func-
       tion.  Typically this is used in SQL  function  definition
       statement.  The form of a parameter is:
       '$' number
       For  example, consider the definition of a function, DEPT,
       as
       create function DEPT (name)
            returns dept
            as 'select * from
                dept where name=$1'
            language 'sql'

Functional Expressions
       A functional expression is the name of a legal  SQL  func-
       tion,  followed by its argument list enclosed in parenthe-
       ses, e.g.:
       fn-name (a_expr{ , a_expr})
       For example, the following computes the square root of  an
       employee salary.
       sqrt(emp.salary)

Aggregate Expression
       An  aggregate  expression  represents  a  simple aggregate
       (i.e., one that computes a single value) or  an  aggregate
       function  (i.e.,  one that computes a set of values).  The
       syntax is the following:
       aggregate.name (attribute)
       Here, aggregate_name must be a previously  defined  aggre-
       gate.

Target_list
       A  target list is a parenthesized, comma-separated list of
       one or more elements, each of which must be of the form:
       a_expr[AS result_attname]
       Here, result_attname is the name of the  attribute  to  be
       created (or an already existing attribute name in the case
       of update statements.)  If result_attname is not  present,
       then  a_expr must contain only one attribute name which is
       assumed to be the name of the result field.   In  Postgres
       default naming is only used if a_expr is an attribute.

Qualification
       A  qualification  consists  of  any number of clauses con-
       nected by the logical operators:
       not
       and
       or
       A clause is an a_expr that evaluates to a Boolean  over  a
       set of instances.

From List
       The  from  list  is a comma-separated list of from expres-
       sions.

       Each from expression is of the form:
       [class_reference] instance_variable
            {, [class_ref] instance_variable...}
       where class_reference is of the form
       class_name [*]
       The from expression defines one or more instance variables
       to range over the class indicated in class_reference.  One
       can also request the instance variable to range  over  all
       classes that are beneath the indicated class in the inher-
       itance hierarchy by postpending the designator "*".

SEE ALSO
       insert(l), delete(l),  execute(l),  update(l),  select(l),
       psql(1).

PostgreSQL                   11/5/95                            1