2 $Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.22 2000/06/20 18:04:18 momjian Exp $
6 <title>SQL Syntax</title>
10 A description of the general syntax of SQL.
15 <acronym>SQL</acronym> manipulates sets of data. The language is
16 composed of various <firstterm>key words</firstterm>. Arithmetic
17 and procedural expressions are allowed. We will cover these topics
18 in this chapter; subsequent chapters will include details on data
19 types, functions, and operators.
23 <title>Key Words</title>
26 <acronym>SQL92</acronym> defines <firstterm>key words</firstterm>
28 which have specific meaning. Some key words are
29 <firstterm>reserved</firstterm>, which indicates that they are
30 restricted to appear in only certain contexts. Other key words are
31 <firstterm>not restricted</firstterm>, which indicates that in certain
33 have a specific meaning but are not otherwise constrained.
37 <productname>Postgres</productname> implements an extended subset of the
38 <acronym>SQL92</acronym> and <acronym>SQL3</acronym> languages. Some language
39 elements are not as restricted in this implementation as is
40 called for in the language standards, in part due
41 to the extensibility features of <productname>Postgres</productname>.
45 Information on <acronym>SQL92</acronym> and <acronym>SQL3</acronym> key words
46 is derived from <xref linkend="DATE97" endterm="DATE97">.
50 <title>Reserved Key Words</title>
53 <acronym>SQL92</acronym> and <acronym>SQL3</acronym> have
54 <firstterm>reserved key words</firstterm> which are not allowed
55 as identifiers and not allowed in any usage other than as fundamental
56 tokens in <acronym>SQL</acronym> statements.
57 <productname>Postgres</productname> has additional key words
58 which have similar restrictions. In particular, these key words
59 are not allowed as column or table names, though in some cases
60 they are allowed to be column labels (i.e. in AS clauses).
65 Any string can be specified as an identifier if surrounded by
66 double quotes (<quote>like this!</quote>). Some care is required since
67 such an identifier will be case sensitive
68 and will retain embedded whitespace and most other special characters.
73 The following are <productname>Postgres</productname>
74 reserved words which are neither <acronym>SQL92</acronym>
75 nor <acronym>SQL3</acronym> reserved words. These are allowed
76 to be present as column labels, but not as identifiers:
81 CLUSTER CONSTRAINT COPY
96 The following are <productname>Postgres</productname>
97 reserved words which are also <acronym>SQL92</acronym>
98 or <acronym>SQL3</acronym> reserved words, and which
99 are allowed to be present as column labels, but not as identifiers:
102 ALL ANY ASC BETWEEN BIT BOTH
103 CASE CAST CHAR CHARACTER CHECK COALESCE COLLATE COLUMN
104 CONSTRAINT CROSS CURRENT CURRENT_DATE CURRENT_TIME
105 CURRENT_TIMESTAMP CURRENT_USER
106 DEC DECIMAL DEFAULT DESC DISTINCT
107 ELSE END EXCEPT EXISTS EXTRACT
108 FALSE FLOAT FOR FOREIGN FROM FULL
111 IN INNER INTERSECT INTO IS
113 LEADING LEFT LIKE LOCAL
114 NATURAL NCHAR NOT NULL NULLIF NUMERIC
115 ON OR ORDER OUTER OVERLAPS
116 POSITION PRECISION PRIMARY PUBLIC
118 SELECT SESSION_USER SOME SUBSTRING
119 TABLE THEN TO TRANSACTION TRIM TRUE
125 The following are <productname>Postgres</productname>
126 reserved words which are also <acronym>SQL92</acronym>
127 or <acronym>SQL3</acronym> reserved words:
132 CASCADE CLOSE COMMIT CREATE CURSOR
133 DECLARE DEFAULT DELETE DESC DISTINCT DROP
134 EXECUTE EXISTS EXTRACT
135 FETCH FLOAT FOR FROM FULL
138 IN INNER INSERT INTERVAL INTO IS
140 LEADING LEFT LIKE LOCAL
141 NAMES NATIONAL NATURAL NCHAR NO NOT NULL
143 PARTIAL PRIMARY PRIVILEGES PROCEDURE PUBLIC
144 REFERENCES REVOKE RIGHT ROLLBACK
147 UNION UNIQUE UPDATE USING
148 VALUES VARCHAR VARYING VIEW
154 The following are <acronym>SQL92</acronym> reserved key words which
155 are not <productname>Postgres</productname> reserved key words, but which
156 if used as function names are always translated into the function
157 <function>CHAR_LENGTH</function>:
165 The following are <acronym>SQL92</acronym> or <acronym>SQL3</acronym>
166 reserved key words which
167 are not <productname>Postgres</productname> reserved key words, but
168 if used as type names are always translated into an alternate, native type:
171 BOOLEAN DOUBLE FLOAT INT INTEGER INTERVAL REAL SMALLINT
176 The following are not keywords of any kind, but when used in the
177 context of a type name are translated into a native
178 <productname>Postgres</productname> type, and when used in the
179 context of a function name are translated into a native function:
185 (translated to <type>TIMESTAMP</type> and <type>INTERVAL</type>,
186 respectively). This feature is intended to help with
187 transitioning to v7.0, and will be removed in the next full
188 release (likely v7.1).
192 The following are either <acronym>SQL92</acronym>
193 or <acronym>SQL3</acronym> reserved key words
194 which are not key words in <productname>Postgres</productname>.
195 These have no proscribed usage in <productname>Postgres</productname>
196 at the time of writing (v7.0) but may become reserved key words in the
201 Some of these key words represent functions in <acronym>SQL92</acronym>.
202 These functions are defined in <productname>Postgres</productname>,
203 but the parser does not consider the names to be key words and they are allowed
209 ALLOCATE ARE ASSERTION AT AUTHORIZATION AVG
211 CASCADED CATALOG CHAR_LENGTH CHARACTER_LENGTH COLLATION
212 CONNECT CONNECTION CONTINUE CONVERT CORRESPONDING COUNT
214 DATE DEALLOCATE DEC DESCRIBE DESCRIPTOR
215 DIAGNOSTICS DISCONNECT DOMAIN
216 ESCAPE EXCEPT EXCEPTION EXEC EXTERNAL
219 IDENTITY INDICATOR INPUT INTERSECT
222 OCTET_LENGTH OPEN OUTPUT OVERLAPS
225 SCHEMA SECTION SESSION SIZE SOME
226 SQL SQLCODE SQLERROR SQLSTATE SUM SYSTEM_USER
227 TEMPORARY TRANSLATE TRANSLATION
236 <title>Non-reserved Keywords</title>
239 <acronym>SQL92</acronym> and <acronym>SQL3</acronym> have
240 <firstterm>non-reserved keywords</firstterm> which have
241 a prescribed meaning in the language but which are also allowed
243 <productname>Postgres</productname> has additional keywords
244 which allow similar unrestricted usage.
245 In particular, these keywords
246 are allowed as column or table names.
250 The following are <productname>Postgres</productname>
251 non-reserved key words which are neither <acronym>SQL92</acronym>
252 nor <acronym>SQL3</acronym> non-reserved key words:
255 ACCESS AFTER AGGREGATE
257 CACHE COMMENT CREATEDB CREATEUSER CYCLE
259 EACH ENCODING EXCLUSIVE
260 FORCE FORWARD FUNCTION
262 INCREMENT INDEX INHERITS INSENSITIVE INSTEAD ISNULL
264 MAXVALUE MINVALUE MODE
265 NOCREATEDB NOCREATEUSER NOTHING NOTIFY NOTNULL
268 RECIPE REINDEX RENAME RETURNS ROW RULE
269 SEQUENCE SERIAL SHARE START STATEMENT STDIN STDOUT
277 The following are <productname>Postgres</productname>
278 non-reserved key words which are <acronym>SQL92</acronym>
279 or <acronym>SQL3</acronym> reserved key words:
284 DAY DEFERRABLE DEFERRED
286 IMMEDIATE INITIALLY INSENSITIVE ISOLATION
292 PENDANT PRIOR PRIVILEGES
293 READ RELATIVE RESTRICT
295 TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TRIGGER
302 The following are <productname>Postgres</productname>
303 non-reserved key words which are also either <acronym>SQL92</acronym>
304 or <acronym>SQL3</acronym> non-reserved key words:
307 COMMITTED SERIALIZABLE TYPE
312 The following are either <acronym>SQL92</acronym>
313 or <acronym>SQL3</acronym> non-reserved key words which are not
314 key words of any kind in <productname>Postgres</productname>:
318 C CATALOG_NAME CHARACTER_SET_CATALOG CHARACTER_SET_NAME
319 CHARACTER_SET_SCHEMA CLASS_ORIGIN COBOL COLLATION_CATALOG
320 COLLATION_NAME COLLATION_SCHEMA COLUMN_NAME
321 COMMAND_FUNCTION CONDITION_NUMBER
322 CONNECTION_NAME CONSTRAINT_CATALOG CONSTRAINT_NAME
323 CONSTRAINT_SCHEMA CURSOR_NAME
324 DATA DATE_TIME_INTERVAL_CODE DATE_TIME_INTERVAL_PRECISION
328 MESSAGE_LENGTH MESSAGE_OCTET_LENGTH MORE MUMPS
331 REPEATABLE RETURNED_LENGTH RETURNED_OCTET_LENGTH
332 RETURNED_SQLSTATE ROW_COUNT
333 SCALE SCHEMA_NAME SERVER_NAME SPACE SUBCLASS_ORIGIN
342 <title>Comments</title>
345 A <firstterm>comment</firstterm>
346 is an arbitrary sequence of characters beginning with double dashes
347 and extending to the end of the line, e.g.:
350 -- This is a standard SQL comment
353 We also support C-style block comments, e.g.:
362 A comment beginning with "/*" extends to the first occurrence of "*/".
371 Names in SQL must begin with a letter
372 (<literal>a</literal>-<literal>z</literal>) or underscore
373 (<literal>_</literal>).
374 Subsequent characters in a name can be letters, digits
375 (<literal>0</literal>-<literal>9</literal>),
376 or underscores. The system uses no more than NAMEDATALEN-1 characters
377 of a name; longer names can be written in queries, but they will be
379 By default, NAMEDATALEN is 32 so the maximum name length is 31 (but
380 at the time the system is built, NAMEDATALEN can be changed in
381 src/include/postgres_ext.h).
385 Names containing other characters may be formed by surrounding them
386 with double quotes (<literal>"</literal>). For example, table or column
388 otherwise disallowed characters such as spaces, ampersands, etc. if
389 quoted. Quoting a name also makes it case-sensitive,
390 whereas unquoted names are always folded to lower case. For example,
391 the names <literal>FOO</literal>, <literal>foo</literal>
392 and <literal>"foo"</literal> are
393 considered the same by <productname>Postgres</productname>, but
394 <literal>"Foo"</literal> is a different name.
398 Double quotes can also be used to protect a name that would otherwise
399 be taken to be an SQL keyword. For example, <literal>IN</literal>
400 is a keyword but <literal>"IN"</literal> is a name.
405 <title>Constants</title>
408 There are three <firstterm>implicitly typed constants</firstterm>
409 for use in <productname>Postgres</productname>: strings, integers,
410 and floating point numbers. Constants can
411 also be specified with explicit types, which can enable more
412 accurate representation and more efficient handling by the
413 backend. The implicit constants are described below; explicit
414 constants are discussed afterwards.
418 <title>String Constants</title>
421 <firstterm>Strings</firstterm>
422 in SQL are arbitrary sequences of ASCII characters bounded by single
423 quotes ("'", e.g. <literal>'This is a string'</literal>).
424 SQL92 allows single quotes to be embedded in strings by typing two
425 adjacent single quotes (e.g. <literal>'Dianne''s horse'</literal>).
426 In <productname>Postgres</productname> single quotes may alternatively
427 be escaped with a backslash ("\", e.g.
428 <literal>'Dianne\'s horse'</literal>). To include a
429 backslash in a string constant, type two backslashes.
430 Non-printing characters may also be embedded within strings by
431 prepending them with a backslash
432 (e.g. <literal>'\<replaceable>tab</replaceable>'</literal>).
438 <title>Integer Constants</title>
441 <firstterm>Integer constants</firstterm>
442 in SQL are collection of ASCII digits with no decimal point. Legal
443 values range from -2147483648 to +2147483647. This will vary
444 depending on the operating system and host machine.
448 Note that larger integers can be specified for <type>int8</type>
449 by using <acronym>SQL92</acronym> string notation or
450 <productname>Postgres</productname> type notation:
453 int8 '4000000000' -- string style
454 '4000000000'::int8 -- Postgres (historical) style
460 <title>Floating Point Constants</title>
463 <firstterm>Floating point constants</firstterm>
464 consist of an integer part, a decimal point, and a fraction part or
465 scientific notation of the following format:
468 {<replaceable>dig</replaceable>}.{<replaceable>dig</replaceable>} [e [+-] {<replaceable>dig</replaceable>}]
471 where <replaceable>dig</replaceable> is one or more digits.
472 You must include at least one <replaceable>dig</replaceable> after the
473 period and after the [+-] if you use those options. An exponent with
474 a missing mantissa has a mantissa of 1 inserted. There may be no
475 extra characters embedded in the string.
479 Floating point constaints are of type
480 <type>float8</type>. <type>float4</type> can be specified
481 explicitly by using <acronym>SQL92</acronym> string notation or
482 <productname>Postgres</productname> type notation:
485 float4 '1.23' -- string style
486 '1.23'::float4 -- Postgres (historical) style
492 <title>Constants of Postgres User-Defined Types</title>
496 <emphasis>arbitrary</emphasis>
497 type can be entered using any one of the following notations:
500 <replaceable>type</replaceable> '<replaceable>string</replaceable>'
501 '<replaceable>string</replaceable>'::<replaceable>type</replaceable>
502 CAST '<replaceable>string</replaceable>' AS <replaceable>type</replaceable>
505 The value inside the string is passed to the input
506 conversion routine for the type called
507 <replaceable>type</replaceable>. The result is a
508 constant of the indicated type. The explicit typecast may be omitted
509 if there is no ambiguity as to the type the constant must be, in which
510 case it is automatically coerced.
515 <title>Array constants</title>
518 <firstterm>Array constants</firstterm>
519 are arrays of any Postgres type, including other arrays, string
520 constants, etc. The general format of an array constant is the
524 {<replaceable>val1</replaceable><replaceable>delim</replaceable><replaceable>val2</replaceable><replaceable>delim</replaceable>}
527 where <replaceable>delim</replaceable>
528 is the delimiter for the type stored in the <literal>pg_type</literal> class.
529 (For built-in types, this is the comma character (","). An
530 example of an array constant is
533 {{1,2,3},{4,5,6},{7,8,9}}
536 This constant is a two-dimensional, 3 by 3 array consisting of three
537 sub-arrays of integers.
541 Individual array elements can and should be placed between quotation
542 marks whenever possible to avoid ambiguity problems with respect to
549 <title>Fields and Columns</title>
552 <title>Fields</title>
555 A <firstterm>field</firstterm>
556 is either an attribute of a given class or one of the following:
563 stands for the unique identifier of an instance which is added by
564 Postgres to all instances automatically. Oids are not reused and are 32
574 The identity of the inserting transaction.
583 The identity of the deleting transaction.
592 The command identifier within the transaction.
601 The identity of the deleting command.
609 For further information on these fields consult
610 <xref linkend="STON87a" endterm="STON87a">.
611 Times are represented internally as instances of the
612 <literal>abstime</literal>
613 data type. Transaction and command identifiers are 32 bit quantities.
614 Transactions are assigned sequentially starting at 512.
619 <title>Columns</title>
622 A <firstterm>column</firstterm> is a construct of the form:
625 <replaceable>instance</replaceable>{.<replaceable>composite_field</replaceable>}.<replaceable>field</replaceable> `['<replaceable>number</replaceable>`]'
628 <replaceable>instance</replaceable>
629 identifies a particular class and can be thought of as standing for
630 the instances of that class. An instance variable is either a class
631 name, a surrogate for a class defined by means of a FROM clause,
632 or the keyword NEW or CURRENT.
633 NEW and CURRENT can only appear in the action portion of a rule, while
634 other instance variables can be used in any SQL statement.
635 <replaceable>composite_field</replaceable>
636 is a field of of one of the Postgres composite types,
637 while successive composite fields address attributes in the
638 class(s) to which the composite field evaluates. Lastly,
639 <replaceable>field</replaceable>
640 is a normal (base type) field in the class(s) last addressed. If
641 <replaceable>field</replaceable>
642 is of type <literal>array</literal>,
643 then the optional <replaceable>number</replaceable>
644 designator indicates a specific element in the array. If no number is
645 indicated, then all array elements are returned.
651 <title>Operators</title>
654 Any built-in system, or user-defined operator may be used in SQL.
655 For the list of built-in and system operators consult
656 <xref linkend="operators" endterm="operators-title">.
657 For a list of user-defined operators consult your system administrator
658 or run a query on the <literal>pg_operator</literal> class.
659 Parentheses may be used for arbitrary grouping of operators in expressions.
664 <title>Expressions</title>
667 <acronym>SQL92</acronym> allows <firstterm>expressions</firstterm>
668 to transform data in tables. Expressions may contain operators
669 (see <xref linkend="operators" endterm="operators-title">
670 for more details) and functions
671 (<xref linkend="functions" endterm="functions-title"> has
676 An expression is one of the following:
679 <member>( a_expr )</member>
680 <member>constant</member>
681 <member>attribute</member>
682 <member><replaceable>a_expr</replaceable> <replaceable>binary_operator</replaceable> <replaceable>a_expr</replaceable></member>
683 <member><replaceable>a_expr</replaceable> <replaceable>right_unary_operator</replaceable></member>
684 <member><replaceable>left_unary_operator</replaceable> <replaceable>a_expr</replaceable></member>
685 <member>parameter</member>
686 <member>functional expression</member>
687 <member>aggregate expression</member>
692 We have already discussed constants and attributes. The three kinds of
693 operator expressions indicate respectively binary (infix), right-unary
694 (suffix) and left-unary (prefix) operators. The following sections
695 discuss the remaining options.
699 <title>Parameters</title>
702 A <firstterm>parameter</firstterm>
703 is used to indicate a parameter in a SQL function. Typically this
704 is used in SQL function definition statement. The form of a
708 $<replaceable class="parameter">number</replaceable>
713 For example, consider the definition of a function,
714 <function>dept</function>, as
717 CREATE FUNCTION dept (name)
727 <title>Functional Expressions</title>
730 A <firstterm>functional expression</firstterm>
731 is the name of a legal SQL function, followed by its argument list
732 enclosed in parentheses:
735 <replaceable>function</replaceable> (<replaceable>a_expr</replaceable> [, <replaceable>a_expr</replaceable> ... ] )
740 For example, the following computes the square root of an employee
750 <title id="aggregates-syntax">Aggregate Expressions</title>
753 An <firstterm>aggregate expression</firstterm> represents the application
754 of an aggregate function across the rows selected by a query.
755 An aggregate function reduces multiple inputs to a single output value,
756 such as the sum or average of the inputs.
757 The syntax of an aggregate expression is one of the following:
760 <member><replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>)</member>
761 <member><replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>)</member>
762 <member><replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)</member>
763 <member><replaceable>aggregate_name</replaceable> ( * )</member>
766 where <replaceable>aggregate_name</replaceable> is a previously defined
767 aggregate, and <replaceable>expression</replaceable> is any expression
768 that doesn't itself contain an aggregate expression.
772 The first form of aggregate expression invokes the aggregate across all
773 input rows for which the given expression yields a non-null value.
774 The second form is the same as the first, since ALL is the default.
775 The third form invokes the aggregate for all distinct non-null values
776 of the expression found in the input rows. The last form invokes the
777 aggregate once for each input row regardless of null or non-null values;
778 since no particular input value is specified, it is generally only useful
779 for the count() aggregate.
783 For example, count(*) yields the total number of input rows;
784 count(f1) yields the number of input rows in which f1 is non-null;
785 count(distinct f1) yields the number of distinct non-null values of f1.
790 <title>Target List</title>
793 A <firstterm>target list</firstterm>
794 is a parenthesized, comma-separated list of one or more elements, each
795 of which must be of the form:
798 <replaceable>a_expr</replaceable> [ AS <replaceable>result_attname</replaceable> ]
801 where <replaceable>result_attname</replaceable>
802 is the name of the attribute to be created (or an
803 already existing attribute name in the case of update statements.) If
804 <replaceable>result_attname</replaceable>
806 <replaceable>a_expr</replaceable>
807 must contain only one attribute name which is assumed to be the name
808 of the result field. In <productname>Postgres</productname>
809 default naming is only used if
810 <replaceable>a_expr</replaceable>
816 <title>Qualification</title>
819 A <firstterm>qualification</firstterm>
820 consists of any number of clauses connected by the logical operators:
828 A clause is an <replaceable>a_expr</replaceable>
829 that evaluates to a <literal>boolean</literal> over a set of instances.
834 <title>From List</title>
837 The <firstterm>from list</firstterm>
838 is a comma-separated list of <firstterm>from expressions</firstterm>.
839 Each "from expression" is of the form:
842 [ <replaceable>class_reference</replaceable> ] <replaceable>instance_variable</replaceable>
843 {, [ <replaceable>class_ref</replaceable> ] <replaceable>instance_variable</replaceable>... }
846 where <replaceable>class_reference</replaceable>
850 <replaceable>class_name</replaceable> [ * ]
853 The "from expression"
854 defines one or more instance variables to range over the class
855 indicated in <replaceable>class_reference</replaceable>.
857 the instance variable to range over only the specific class
858 and not those that are beneath the
859 indicated class in the inheritance hierarchy by specifying ONLY before
860 before the classname.
866 <!-- Keep this comment at the end of the file
871 sgml-minimize-attributes:nil
872 sgml-always-quote-attributes:t
875 sgml-parent-document:nil
876 sgml-default-dtd-file:"./reference.ced"
877 sgml-exposed-tags:nil
878 sgml-local-catalogs:("/usr/lib/sgml/catalog")
879 sgml-local-ecat-files:nil