diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/commands.sgml | 6 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_operator.sgml | 416 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 363 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_sequence.sgml | 317 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 1304 |
5 files changed, 2405 insertions, 1 deletions
diff --git a/doc/src/sgml/ref/commands.sgml b/doc/src/sgml/ref/commands.sgml index f295a942793..3edc715778d 100644 --- a/doc/src/sgml/ref/commands.sgml +++ b/doc/src/sgml/ref/commands.sgml @@ -16,6 +16,10 @@ &createFunction; &createIndex; &createLanguage; +&createOperator; +&createRule; +&createSequence; +&createTable; &dropFunction; &select; @@ -36,4 +40,4 @@ sgml-exposed-tags:nil sgml-local-catalogs:"/usr/lib/sgml/catalog" sgml-local-ecat-files:nil End: --->
\ No newline at end of file +--> diff --git a/doc/src/sgml/ref/create_operator.sgml b/doc/src/sgml/ref/create_operator.sgml new file mode 100644 index 00000000000..a9c25a02692 --- /dev/null +++ b/doc/src/sgml/ref/create_operator.sgml @@ -0,0 +1,416 @@ +<REFENTRY ID="SQL-CREATEOPERATOR-1"> + <REFMETA> + <REFENTRYTITLE> + CREATE OPERATOR + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + CREATE OPERATOR + </REFNAME> + <REFPURPOSE> + Defines a new user operator. + </REFPURPOSE> + + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CREATE OPERATOR <replaceable>name</replaceable> + ([ LEFTARG = <replaceable class="parameter">type1</replaceable> ] + [, RIGHTARG = <replaceable class="parameter">type2</replaceable> ] + , PROCEDURE = <replaceable class="parameter">func_name</replaceable> + [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] + [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ] + [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] + [, HASHES ] + [, JOIN = <replaceable class="parameter">join_proc</replaceable> ] + [, SORT = <replaceable class="parameter">sort_op</replaceable> [, ...] ] + ) + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CREATEOPERATOR-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">name</replaceable> + </TERM> + <LISTITEM> + <PARA> + The name of an existing aggregate function. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">type1</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">type2</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">func_name</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">com_op</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">neg_op</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">res_proc</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">join_proc</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">sort_op</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CREATEOPERATOR-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATE</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if the operator is successfully created. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CREATEOPERATOR-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + This command defines a new user operator, operator_name. + The user who defines an operator becomes its owner. + </para> + <para> + The operator_name is a sequence of up to sixteen punctua + tion characters. The following characters are valid for + single-character operator names:<literallayout> + + ~ ! @ # % ^ & ` ? </literallayout> + </para> + <para> + If the operator name is more than one character long, it + may consist of any combination of the above characters or + the following additional characters:<literallayout> + + | $ : + - * / < > =</literallayout> + </para> + <para> + The operator "!=" is mapped to "<>" on input, and they are + therefore equivalent. + </para> + <para> + At least one of leftarg and rightarg must be defined. For + binary operators, both should be defined. For right unary + operators, only arg1 should be defined, while for left + unary operators only arg2 should be defined. + </para> + <para> + The name of the operator, operator_name, can be composed + of symbols only. Also, the func_name procedure must have + been previously defined using create function(l) and must + have one or two arguments. + </para> + <para> + The commutator operator is present so that Postgres can + reverse the order of the operands if it wishes. For exam + ple, the operator area-less-than, >>>, would have a commu + tator operator, area-greater-than, <<<. Suppose that an + operator, area-equal, ===, exists, as well as an area not + equal, !==. Hence, the query optimizer could freely con + vert: + <programlisting> + "0,0,1,1"::box >>> MYBOXES.description + </programlisting> + to + <programlisting> + MYBOXES.description <<< "0,0,1,1"::box</programlisting> + </para> + <para> + This allows the execution code to always use the latter + representation and simplifies the query optimizer some + what. + </para> + <para> + The negator operator allows the query optimizer to convert + <programlisting> + NOT MYBOXES.description === "0,0,1,1"::box + </programlisting> + to + <programlisting> + MYBOXES.description !== "0,0,1,1"::box + </programlisting> + </para> + <para> + If a commutator operator name is supplied, Postgres + searches for it in the catalog. If it is found and it + does not yet have a commutator itself, then the commutator's + entry is updated to have the current (new) operator + as its commutator. This applies to the negator, as well. + </para> + <para> + This is to allow the definition of two operators that are + the commutators or the negators of each other. The first + operator should be defined without a commutator or negator + (as appropriate). When the second operator is defined, + name the first as the commutator or negator. The first + will be updated as a side effect. + </para> + <para> + The next two specifications are present to support the + query optimizer in performing joins. Postgres can always + evaluate a join (i.e., processing a clause with two tuple + variables separated by an operator that returns a boolean) + by iterative substitution [WONG76]. In addition, Postgres + is planning on implementing a hash-join algorithm along + the lines of [SHAP86]; however, it must know whether this + strategy is applicable. For example, a hash-join + algorithm is usable for a clause of the form: + <programlisting> + MYBOXES.description === MYBOXES2.description + </programlisting> + but not for a clause of the form: + <programlisting> + MYBOXES.description <<< MYBOXES2.description. + </programlisting> + The hashes flag gives the needed information to the query + optimizer concerning whether a hash join strategy is + usable for the operator in question.</para> + <para> + Similarly, the two sort operators indicate to the query + optimizer whether merge-sort is a usable join strategy and + what operators should be used to sort the two operand + classes. For the === clause above, the optimizer must + sort both relations using the operator, <<<. On the other + hand, merge-sort is not usable with the clause: + <programlisting> + MYBOXES.description <<< MYBOXES2.description + </programlisting> + </para> + <para> + If other join strategies are found to be practical, Post + gres will change the optimizer and run-time system to use + them and will require additional specification when an + operator is defined. Fortunately, the research community + invents new join strategies infrequently, and the added + generality of user-defined join strategies was not felt to + be worth the complexity involved. + </para> + <para> + The last two pieces of the specification are present so + the query optimizer can estimate result sizes. If a + clause of the form: + <programlisting> + MYBOXES.description <<< "0,0,1,1"::box + </programlisting> + is present in the qualification, then Postgres may have to + estimate the fraction of the instances in MYBOXES that + satisfy the clause. The function res_proc must be a reg + istered function (meaning it is already defined using + define function(l)) which accepts one argument of the correct + data type and returns a floating point number. The + query optimizer simply calls this function, passing the + parameter "0,0,1,1" and multiplies the result by the relation + size to get the desired expected number of instances. + </para> + <para> + Similarly, when the operands of the operator both contain + instance variables, the query optimizer must estimate the + size of the resulting join. The function join_proc will + return another floating point number which will be multiplied + by the cardinalities of the two classes involved to + compute the desired expected result size. + </para> + <para> + The difference between the function + <programlisting> + my_procedure_1 (MYBOXES.description, "0,0,1,1"::box) + </programlisting> + and the operator + <programlisting> + MYBOXES.description === "0,0,1,1"::box + </programlisting> + is that Postgres attempts to optimize operators and can + decide to use an index to restrict the search space when + operators are involved. However, there is no attempt to + optimize functions, and they are performed by brute force. + Moreover, functions can have any number of arguments while + operators are restricted to one or two. + </PARA> + + <REFSECT2 ID="R2-SQL-CREATEOPERATOR-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Refer to <citetitle>PostgreSQL User's Guide</citetitle> chapter 5 + <comment> + This reference must be corrected. + </comment> + for further information. + Refer to DROP OPERATOR statement to drop operators. + + </REFSECT2> + + <REFSECT1 ID="R1-SQL-CREATEOPERATOR-2"> + <TITLE> + Usage + </TITLE> + <PARA>The following command defines a new operator, + area-equality, for the BOX data type. + </PARA> + <ProgramListing> + CREATE OPERATOR === ( + LEFTARG = box, + RIGHTARG = box, + PROCEDURE = area_equal_procedure, + COMMUTATOR = ===, + NEGATOR = !==, + RESTRICT = area_restriction_procedure, + HASHES, + JOIN = area-join-procedure, + SORT = <<<, <<<) + </ProgramListing> + + + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CREATEOPERATOR-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + CREATE OPERATOR is a PostgreSQL extension of SQL. + </PARA> + + <REFSECT2 ID="R2-SQL-CREATEOPERATOR-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + There is no CREATE OPERATOR statement on SQL92. + </PARA> + </refsect2> + </refsect1> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +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: +--> diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml new file mode 100644 index 00000000000..93ef6057de7 --- /dev/null +++ b/doc/src/sgml/ref/create_rule.sgml @@ -0,0 +1,363 @@ +<REFENTRY ID="SQL-CREATERULE-1"> + <REFMETA> + <REFENTRYTITLE> + CREATE RULE + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + CREATE RULE + </REFNAME> + <REFPURPOSE> + Defines a new rule. + </REFPURPOSE> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CREATE RULE <replaceable class="parameter">name</replaceable> + AS ON <replaceable class="parameter">event</replaceable> + TO <replaceable class="parameter">object</replaceable> [WHERE <replaceable class="parameter">condition</replaceable>] + DO [INSTEAD] + [<replaceable class="parameter">action</replaceable> | NOTHING ] + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CREATERULE-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">name</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The name of a rule to create. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">event</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Event is one of <literal>select</literal>, <literal>update</literal>, <literal>delete</literal> or <literal>insert</literal>. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">object</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Object is either <replaceable class="parameter">table</replaceable> or <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable>. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">condition</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Any SQL <literal>where</literal> clause. <literal>new</literal> or + <literal>current</literal> can appear instead of an instance + variable whenever an instance variable is permissible in SQL. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">action</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Any SQL-statement. <literal>new</literal> or + <literal>current</literal> can appear instead of an instance + variable whenever an instance variable is permissible in SQL. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CREATERULE-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATE</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if the rule is successfully created. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CREATERULE-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + The semantics of a rule is that at the time an individual instance is + accessed, updated, inserted or deleted, there is a current instance (for + retrieves, updates and deletes) and a new instance (for updates and + appends). If the <replaceable class="parameter">event</replaceable> + specified in the <literal>on</literal> clause and the + <replaceable class="parameter">condition</replaceable> specified in the + <literal>where</literal> clause are true for the current instance, the + <replaceable class="parameter">action</replaceable> part of the rule is + executed. First, however, values from fields in the current instance + and/or the new instance are substituted for + <literal> current.</literal><replaceable class="parameter">attribute-name</replaceable> + and <literal>new.</literal><replaceable class="parameter">attribute-name</replaceable>. + </para> + <para> + The <replaceable class="parameter">action</replaceable> part of the rule + executes with the same command and transaction identifier as the user + command that caused activation. + </para> + + <REFSECT2 ID="R2-SQL-CREATERULE-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <para> + A note of caution about SQL rules is in order. If the same class name + or instance variable appears in the + <replaceable class="parameter">event</replaceable>, the + <replaceable class="parameter">condition</replaceable> and the + <replaceable class="parameter">action</replaceable> parts of a rule, + they are all considered different tuple variables. More accurately, + <literal>new</literal> and <literal>current</literal> are the only tuple + variables that are shared between these clauses. For example, the following + two rules have the same semantics: + <programlisting> + on update to EMP.salary where EMP.name = "Joe" + do update EMP ( ... ) where ... + + on update to EMP-1.salary where EMP-2.name = "Joe" + do update EMP-3 ( ... ) where ... + </programlisting> + Each rule can have the optional tag <literal>instead</literal>. Without + this tag, <replaceable class="parameter">action</replaceable> will be + performed in addition to the user command when the + <replaceable class="parameter">event</replaceable> in the + <replaceable class="parameter">condition</replaceable> part of the rule + occurs. Alternately, the + <replaceable class="parameter">action</replaceable> part will be done + instead of the user command. In this later case, the + <replaceable class="parameter">action</replaceable> can be the keyword + <literal>nothing</literal>. + </para> + <para> + When choosing between the rewrite and instance rule systems for a + particular rule application, remember that in the rewrite system, + <literal>current</literal> refers to a relation and some qualifiers + whereas in the instance system it refers to an instance (tuple). + </para> + <para> + It is very important to note that the rewrite rule system + will neither detect nor process circular rules. For example, though each + of the following two rule definitions are accepted by Postgres, the + retrieve command will cause Postgres to crash: + <example> + <title>Example of a circular rewrite rule combination.</title> + <programlisting> + create rule bad_rule_combination_1 is + on select to EMP + do instead select to TOYEMP + + create rule bad_rule_combination_2 is + on select to TOYEMP + do instead select to EMP + </programlisting> + <para> + This attempt to retrieve from EMP will cause Postgres to crash. + <programlisting> + select * from EMP + </programlisting></para> + </example> + </para> + <para> + You must have rule definition access to a class in order + to define a rule on it (see change acl(l)). + <comment> + There is no manpage change or change_acl. What is intended? + </comment> + </PARA> + </REFSECT2> + </refsect1> + + <REFSECT1 ID="R1-SQL-CREATERULE-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Make Sam get the same salary adjustment as Joe + + <programlisting> + create rule example_1 is + on update EMP.salary where current.name = "Joe" + do update EMP (salary = new.salary) + where EMP.name = "Sam" + </programlisting> + + At the time Joe receives a salary adjustment, the event + will become true and Joe's current instance and proposed + new instance are available to the execution routines. + Hence, his new salary is substituted into the action part + of the rule which is subsequently executed. This propagates + Joe's salary on to Sam. + </para> + <para> + Make Bill get Joe's salary when it is accessed + <programlisting> + create rule example_2 is + + on select to EMP.salary + where current.name = "Bill" + do instead + select (EMP.salary) from EMP where EMP.name = "Joe" + </programlisting> + </para> + <para> + Deny Joe access to the salary of employees in the shoe + department. (<function>pg_username()</function> returns the name of + the current user) + <programlisting> + create rule example_3 is + on select to EMP.salary + where current.dept = "shoe" and pg_username() = "Joe" + do instead nothing + </programlisting> + </para> + <para> + Create a view of the employees working in the toy department. + <programlisting> + create TOYEMP(name = char16, salary = int4) + + create rule example_4 is + on select to TOYEMP + do instead select (EMP.name, EMP.salary) from EMP + where EMP.dept = "toy" + </programlisting> + </para> + <para> + All new employees must make 5,000 or less + <programlisting> + create rule example_5 is + on insert to EMP where new.salary > 5000 + do update newset salary = 5000 + </programlisting> + </PARA> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CREATERULE-3"> + <TITLE> + Bugs + </TITLE> + <PARA> + <literal>instead</literal> rules do not work properly. + </para> + <para> + The object in a SQL rule cannot be an array reference and + cannot have parameters. + </para> + <para> + Aside from the "oid" field, system attributes cannot be + referenced anywhere in a rule. Among other things, this + means that functions of instances (e.g., "<literal>foo(emp)</literal>" where + "<literal>emp</literal>" is a class) cannot be called anywhere in a rule. + </para> + <para> + The rule system stores the rule text and query plans as + text attributes. This implies that creation of rules may + fail if the rule plus its various internal representations + exceed some value that is on the order of one page (8KB). + </PARA> + + <REFSECT1 ID="R1-SQL-CREATERULE-4"> + <TITLE> + Compatibility + </TITLE> + <PARA> + CREATE RULE statement is a PostgreSQL language extension. + </PARA> + + <REFSECT2 ID="R2-SQL-CREATERULE-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <para> + There is no CREATE RULE statement in SQL92. + </para> + </refsect2> + </refsect1> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +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: +--> diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml new file mode 100644 index 00000000000..bad6ef36aaf --- /dev/null +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -0,0 +1,317 @@ +<REFENTRY ID="SQL-CREATESEQUENCE-1"> + <REFMETA> + <REFENTRYTITLE> + CREATE SEQUENCE + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + CREATE SEQUENCE + </REFNAME> + <REFPURPOSE> + creates a new sequence number generator. + </REFPURPOSE> + + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CREATE SEQUENCE <replaceable class="parameter">seqname</replaceable> + [INCREMENT <replaceable class="parameter">increment</replaceable>] + [MINVALUE <replaceable class="parameter">minvalue</replaceable>] + [MAXVALUE <replaceable class="parameter">maxvalue</replaceable>] + [START <replaceable class="parameter">start</replaceable>] + [CACHE <replaceable class="parameter">cache</replaceable>] + [CYCLE] + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CREATESEQUENCE-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">seqname</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The name of a sequence to be created. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">increment</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The <option>INCREMENT <replaceable class="parameter">increment</replaceable></option> clause is optional. A positive value will make an + ascending sequence, a negative one a descending sequence. The default value + is 1. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">minvalue</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The optional clause <option>MINVALUE + <replaceable class="parameter">minvalue</replaceable></option> + determines the minimum value + a sequence can be. The defaults are 1 and -2147483647 for + ascending and descending sequences, respectively. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">maxvalue</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Use the optional clause <option>MAXVALUE + <replaceable class="parameter">maxvalue</replaceable></option> to + determine the maximum + value for the sequence. The defaults are 2147483647 and -1 for + ascending and descending sequences, respectively. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">start</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The optional <option>START + <replaceable class="parameter">start</replaceable> + clause</option> enables the sequence to begin anywhere. + The default starting value is + <replaceable class="parameter">minvalue</replaceable> + for ascending sequences and + <replaceable class="parameter">maxvalue</replaceable> + for descending ones. + <comment> + What happens if the user specifies start outside the range? + </comment> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">cache</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The <option>CACHE <replaceable class="parameter">cache</replaceable></option> option + enables sequence numbers to be preallocated + and stored in memory for faster access. The minimum + value is 1 (no cache) and this is also the default. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>CYCLE</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The optional CYCLE keyword may be used to enable the sequence + to continue when the + <replaceable class="parameter">maxvalue</replaceable> or + <replaceable class="parameter">minvalue</replaceable> has been + reached by + an ascending or descending sequence respectively. If the limit is + reached, the next number generated will be whatever the + <replaceable class="parameter">minvalue</replaceable> or + <replaceable class="parameter">maxvalue</replaceable> is, + as appropriate. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CREATESEQUENCE-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATE</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if the command is successful. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>ERROR: amcreate: '<replaceable class="parameter"> seqname</replaceable>' relation already exists</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + If the sequence specified already exists. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CREATESEQUENCE-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + CREATE SEQUENCE will enter a new sequence number generator + into the current data base. This involves creating and initialising a + new single block + table with the name <replaceable class="parameter">seqname</replaceable>. + The generator will be "owned" by the user issuing the command. + </PARA> + <para> + After the sequence is created, you may use the function + <function>nextval()</function> with the + sequence name as the argument to get a new number from the sequence. + The function <function>currval('<replaceable class="parameter">sequence_name</replaceable>')</function> may be used + to determine the number returned by the last call to + <function>nextval()</function> for the + specified sequence in the current session. + </para> + + <para> + Use a query like + <programlisting> + SELECT * FROM sequence_name; + </programlisting> + to get the parameters of a sequence. + </para> + <para> + Low-level locking is used to enable multiple simultaneous + calls to a generator. + </para> + + <REFSECT2 ID="R2-SQL-CREATESEQUENCE-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Refer to the DROP SEQUENCE statement to remove a sequence. + </PARA> + <para> + Each backend uses its own cache to store allocated numbers. + Numbers that are cached but not used in the current session will be + lost. + </para> + </REFSECT2> + </refsect1> + + <REFSECT1 ID="R1-SQL-CREATESEQUENCE-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Create an ascending sequence called serial, starting at 101: + </PARA> + <ProgramListing> + CREATE SEQUENCE serial START 101; + </ProgramListing> + <para> + Select the next number from this sequence + <programlisting> + SELECT NEXTVAL ('serial'); + + nextval + ------- + 114 + </programlisting> + </para> + <para> + Use this sequence in an INSERT: + <programlisting> + INSERT INTO distributors VALUES (NEXTVAL ('serial'),'nothing'); + </programlisting> + </para> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CREATESEQUENCE-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + CREATE SEQUENCE statement is a PostgreSQL language extension. + </PARA> + + <REFSECT2 ID="R2-SQL-CREATESEQUENCE-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + There is no CREATE SEQUENCE statement on SQL92. + </PARA> + </refsect2> + </refsect1> +</REFENTRY> + + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +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: +--> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml new file mode 100644 index 00000000000..9e19e139977 --- /dev/null +++ b/doc/src/sgml/ref/create_table.sgml @@ -0,0 +1,1304 @@ +<REFENTRY ID="SQL-CREATETABLE-1"> + <REFMETA> + <REFENTRYTITLE> + CREATE TABLE + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + CREATE TABLE + </REFNAME> + <REFPURPOSE> + Creates a new table. + </REFPURPOSE> + + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CREATE TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> ( + <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE> [DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>] [CONSTRAINT <REPLACEABLE CLASS="PARAMETER">column_constraint</REPLACEABLE>] [, ...] + [, <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> ...] + [, CONSTRAINT <REPLACEABLE CLASS="PARAMETER">table_constraint</REPLACEABLE>] + ) [INHERITS ( <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE> [, ...] )] + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CREATETABLE-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + The name of a new table to be created. + </PARA> + </LISTITEM> + </VARLISTENTRY> + + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + The name of a column. + </PARA> + </LISTITEM> + </VARLISTENTRY> + + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + The type of the column. + (Refer to the <ProductName>Postgres</ProductName> User's Guide for + further information about data types). + </PARA> + </LISTITEM> + </VARLISTENTRY> + + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + A default value for a column. + See the DEFAULT clause for more information. + </PARA> + </LISTITEM> + </VARLISTENTRY> + + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + The optional INHERITS clause specifies a collection of table + names from which this table automatically inherits all fields. + If any inherited field name appears more than once, PostgreSQL + reports an error. PostgreSQL automatically allows the created + table to inherit functions on tables above it in the inheritance + hierarchy. Inheritance of functions is done according + to the conventions of the Common Lisp Object System (CLOS). + </PARA> + </LISTITEM> + </VARLISTENTRY> + + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">column_constraint</REPLACEABLE> + <REPLACEABLE CLASS="PARAMETER">table_constraint</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + The optional CONSTRAINT clause specifies a list of integrity + constraints which new or updated entries must satisfy for + an insert or update operation to succeed. Each constraint + must evaluate to a boolean expression. Multiple columns + may be referenced within a single constraint. + See CONSTRAINT clause for more information. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + + + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CREATETABLE-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>status</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATE</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if table is successfully created. + </PARA> + </LISTITEM> + </VARLISTENTRY> + + <VARLISTENTRY> + <TERM> + <ReturnValue>ERROR</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if table creation failed. + This is usually accompanied by some descriptive text, such as: + <ProgramListing> + amcreate: "<replaceable class="parameter">table</replaceable>" relation already exists + </ProgramListing> + which occurs at runtime, if the table specified already exists + in the database. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CREATETABLE-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + CREATE TABLE will enter a new table into the current data + base. The table will be "owned" by the user issuing the + command. + + <PARA> + The new table is created as a heap with no initial data. + A table can have no more than 1600 columns (realistically, + this is limited by the fact that tuple sizes must + be less than 8192 bytes), but this limit may be configured + lower at some sites. A table cannot have the same name as + a system catalog table. + </PARA> + + <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + DEFAULT clause + </TITLE> + <PARA> + <SYNOPSIS> + DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE> + </SYNOPSIS> + <PARA> + The DEFAULT clause assigns a default data value to a column. + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">value</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The possible values for expression are: + <itemizedlist> + <listitem> + <simpara> + a literal value + </simpara> + </listitem> + <listitem> + <simpara> + a user function + </simpara> + </listitem> + <listitem> + <simpara> + a niladic function + </simpara> + </listitem> + </itemizedlist> + </para> + </listitem> + </VARLISTENTRY> + + <VARLISTENTRY> + <TERM> + <ReturnValue>ERROR: DEFAULT: type mismatched</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + if data type of default value doesn't match the + column definition's data type. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + <PARA> + The DEFAULT clause assigns a default data value to a column + (via a column definition in the CREATE TABLE statement). + The data type of a default value must match the column definition's + data type. + </PARA> + <PARA> + An INSERT operation that includes a column without a specified + default value will assign the NULL value to the column + if no explicit data value is provided for it. + Default <replaceable class="parameter">literal</replaceable> means + that the default is the specified constant value. + Default <replaceable class="parameter">niladic-function</replaceable> + or <replaceable class="parameter">user-function</replaceable> means + that the default + is the value of the specified function at the time of the INSERT. + </PARA> + <PARA> + There are two types of niladic functions:<variablelist> + <varlistentry> + <term>niladic USER</term> + <listitem> + <variablelist> + <varlistentry> + <term>CURRENT_USER / USER</term> + <listitem> + <simpara>See CURRENT_USER function</simpara> + </listitem> + </varlistentry> + <varlistentry> + <term>SESSION_USER</term> + <listitem> + <simpara>not yet supported</simpara> + </listitem> + </varlistentry> + <varlistentry> + <term>SYSTEM_USER</term> + <listitem> + <simpara>not yet supported</simpara> + </listitem> + </varlistentry> + </variablelist> + </listitem> + </varlistentry> + <varlistentry> + <term>niladic datetime</term> + <listitem> + <variablelist> + <varlistentry> + <term> CURRENT_DATE</term> + <listitem> + <simpara>See CURRENT_DATE function</simpara> + </listitem> + </varlistentry> + <varlistentry> + <term>CURRENT_TIME</term> + <listitem> + <simpara>See CURRENT_TIME function</simpara> + </listitem> + </varlistentry> + <varlistentry> + <term>CURRENT_TIMESTAMP</term> + <listitem> + <simpara>See CURRENT_TIMESTAMP function</simpara> + </listitem> + </varlistentry> + </variablelist> + </listitem> + </varlistentry> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-NOTNULL-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + NOT NULL constraint + </TITLE> + <SYNOPSIS> + [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] NOT NULL + </SYNOPSIS> + <PARA> + The NOT NULL constraint specifies a rule that a column may + contain only non-null values. + </PARA> + <PARA> + The NOT NULL constraint is a column constraint. + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">name</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The optional name of a constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + + <REFSECT3 ID="R3-SQL-NOTNULL-1"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>ERROR: ExecAppend: Fail to add null value in not + null attribute "<replaceable class="parameter">column</replaceable>".</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + This error occurs at runtime if one tries to insert a null value + into a column which has a NOT NULL constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT3> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-UNIQUECLAUSE-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + UNIQUE constraint + </TITLE> + <para> + Table Constraint definition + </para> + <synopsis> + [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ...] ) + </SYNOPSIS> + <para> + Column Constraint definition + </para> + <synopsis> + [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE + </SYNOPSIS> + <refsect3> + <title>Parameters</title> + <variablelist> + <varlistentry> + <term> + <replaceable class="parameter">name</replaceable> + </term> + <listitem> + <para> + An arbitrary name given to a constraint. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term> + <replaceable class="parameter">column</replaceable> + </term> + <listitem> + <para> + A name of a column in a table. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect3> + <refsect3> + <title>Outputs</title> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + ERROR: Cannot insert a duplicate key into a unique index. + </term> + <listitem> + <para> + This error occurs at runtime if one tries to insert a + duplicate value into a column. + </para> + </listitem> + </varlistentry> + </variablelist></para> + </listitem> + </varlistentry> + </variablelist> + </refsect3> + <refsect3> + <title>Description</title> + <PARA> + The UNIQUE constraint specifies a rule that a group of one or + more distinct columns of a table may contain only unique values. + </para> + <para> + The column definitions of the specified columns do not have to + include a NOT NULL constraint to be included in a UNIQUE + constraint. Having more than one null value in a column without a + NOT NULL constraint, does not violate a UNIQUE constraint. + </PARA> + <PARA> + Each UNIQUE constraint must name a set of columns that is + different from the set of columns named by any other UNIQUE or + PRIMARY KEY constraint defined for the Table. + </PARA> + <Note> + <Para> + PostgreSQL automatically creates a unique index for each UNIQUE + constraint, to assure + data integrity. See CREATE INDEX for more information. + </Para> + </Note> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CONSTRAINT-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + CONSTRAINT clause + </TITLE> + <para> + Table constraint definition + </para> + <SYNOPSIS> + [ CONSTRAINT name ] + { PRIMARY KEY constraint | + UNIQUE constraint | + CHECK constraint } + </SYNOPSIS> + <PARA> + Column constraint definition + </PARA> + <SYNOPSIS> + [ CONSTRAINT name ] + { NOT NULL constraint | + PRIMARY KEY constraint | + UNIQUE constraint | + CHECK constraint } + </SYNOPSIS> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue> + <replaceable class="parameter">name</replaceable> + </ReturnValue> + </TERM> + <LISTITEM> + <PARA> + An arbitrary name given to an integrity constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue> + <replaceable class="parameter">constraint</replaceable> + </ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The definition of the constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + <para> + A Constraint is a named rule: a SQL object which helps define + valid sets of values by putting limits on the results of INSERT, + UPDATE or DELETE operations performed on a Base table. + </para> + <para> + There are two ways to define integrity constraints: + Table constraint and Column constraint. + </para> + <para> + A Table Constraint is an integrity Constraint defined on one or + more Columns of a Base table. The four variations of "Table + Constraint" are: + <simplelist columns="1"> + <member>PRIMARY KEY</member> + <member>FOREIGN KEY</member> + <member>UNIQUE</member> + <member>CHECK</member> + </simplelist> + </para> + <para> + A column constraint is an integrity constraint defined as part + of a column definition, and logically becomes a table + constraint as soon as it is created. The column + constraints available are: + <simplelist columns="1"> + <member>PRIMARY KEY</member> + <member>REFERENCES</member> + <member>UNIQUE</member> + <member>CHECK</member> + <member>NOT NULL</member> + </simplelist></para> + <note> + <para> + PostgreSQL does not yet (at release 6.3.2) support the FOREIGN KEY or + REFERENCES integrity constraints, although the parser will accept them. + Foreign keys may be partially emulated by triggers (See CREATE TRIGGER + statement) + </para> + </note> + <note> + <para> + PostgreSQL does not yet support either DOMAINs or ASSERTIONs. + </para> + </note> + + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CHECK-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <title>The CHECK constraint</title> + <SYNOPSIS> + [ CONSTRAINT name ] CHECK ( condition [, ...] ) + </SYNOPSIS> + <refsect3 id="R3-SQL-CHECK-1"> + <title>Inputs</title> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">name</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + An arbitrary name given to a constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>condition</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Any valid conditional expression. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </REFSECT3> + + <REFSECT3 ID="R3-SQL-CHECK-2"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue> + ERROR: ExecAppend: rejected due to CHECK constraint + "<replaceable class="parameter">table_column</replaceable>". + </ReturnValue> + </TERM> + <LISTITEM> + <PARA> + This error occurs at runtime if one tries to insert an illegal + value into a column subject to a CHECK constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </REFSECT3> + <refsect3> + <title>Description</title> + <para> + The CHECK constraint specifies a rule that a group of one or + more columns of a table may contain only those values allowed by + the rule. + </para> + <PARA> + The CHECK constraint is either a table constraint or a column + constraint. + </PARA> + <PARA> + PostgreSQL automatically creates an unique index to assure + data integrity. (See CREATE INDEX statement) + </PARA> + <PARA> + The SQL92 CHECK column constraints can only be defined on, and + refer to, one column of the table. PostgreSQL does not have + this restriction. + </PARA> + </refsect3> + <refsect3> + <title>BUGS in CHECK constraint</title> + <PARA> + The following CHECK constraints gives a parse error like: + <programlisting> + ERROR: parser: parse error at or near "opname": + </programlisting> + <simplelist columns="1"> + <member>CHECK ( <replaceable class="parameter">column</replaceable> BETWEEN 'A' AND 'Z' )</member> + <member>CHECK ( <replaceable class="parameter">column</replaceable> IN ('A','Z'))</member> + <member>CHECK ( <replaceable class="parameter">column</replaceable> NOT LIKE 'A%')</member> + </simplelist> + </para> + </refsect3> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-PRIMARYKEY-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + PRIMARY KEY clause + </TITLE> + <PARA> + Table constraint definition + </PARA> + <SYNOPSIS> + [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) + </SYNOPSIS> + <PARA> + Column constraint definition + </PARA> + <SYNOPSIS> + [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY + </SYNOPSIS> + + <refsect3> + <title>Parameters</title> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + An arbitrary name for the constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The name of a column in the table. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </para> + </refsect3> + <refsect3> + <title>Outputs</title> + <variablelist> + <varlistentry> + <term>ERROR: Cannot insert a duplicate key into a unique index.</term> + <listitem> + <para> + This occurs at run-time if one tries to insert a duplicate value into + a column subject to a PRIMARY KEY constraint. + </PARA> + </listitem> + </varlistentry> + </variablelist> + </refsect3> + <refsect3> + <title>Description</title> + <PARA> + The PRIMARY KEY constraint specifies a rule that a group of one + or more distinct columns of a table may contain only unique, + (not duplicates), non-null values. The column definitions of + the specified columns do not have to include a NOT NULL + constraint to be included in a PRIMARY KEY constraint. + </PARA> + <PARA> + A table's set of valid values may be constrained by only one + PRIMARY KEY constraint at a time. + </PARA> + <PARA> + The PRIMARY KEY constraint must name a set of columns that is + different from the set of columns named by any UNIQUE constraint + defined for the same table. + </PARA> + </REFSECT3> + + <REFSECT3 ID="R3-SQL-PRIMARYKEY-3"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + Notes + </TITLE> + <PARA> + PostgreSQL automatically creates an unique index to assure + data integrity. (See CREATE INDEX statement) + </PARA> + </refsect3> + + </REFSECT2> + </refsect1> + + <REFSECT1 ID="R1-SQL-CREATETABLE-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Create table films and table distributors + </PARA> + <ProgramListing> + CREATE TABLE films ( + code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY, + title CHARACTER VARYING(40) NOT NULL, + did DECIMAL(3) NOT NULL, + date_prod DATE, + kind CHAR(10), + len INTERVAL HOUR TO MINUTE + ); + </ProgramListing> + + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(03) PRIMARY KEY DEFAULT NEXTVAL('serial'), + name VARCHAR(40) NOT NULL CHECK (name <> '') + ); + </ProgramListing> + + <PARA> + Create a table with a 2-dimensional array + </PARA> + <ProgramListing> + CREATE TABLE array ( + vector INT[][] + ); + </ProgramListing> + + <PARA> + Define two NOT NULL column constraints on the table distributors + </PARA> + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(3) CONSTRAINT no_null NOT NULL, + name VARCHAR(40) NOT NULL + ); + </ProgramListing> + + <PARA> + Define a UNIQUE table constraint for the table films. + UNIQUE table constraints can be defined on one or more + columns of the table + </PARA> + <ProgramListing> + CREATE TABLE films ( + code CHAR(5), + title VARCHAR(40), + did DECIMAL(03), + date_prod DATE, + kind CHAR(10), + len INTERVAL HOUR TO MINUTE, + CONSTRAINT production UNIQUE(date_prod) + ); + </ProgramListing> + + <PARA> + Defines a UNIQUE column constraint for the table distributors. + UNIQUE column constraints can only be defined on one column + of the table (the following two examples are equivalents). + </PARA> + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(03), + name VARCHAR(40) UNIQUE, + UNIQUE(name) + ); + </ProgramListing> + + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(3), + name VARCHAR(40) UNIQUE + ); + </ProgramListing> + + <PARA> + Define a CHECK column constraint. + </PARA> + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(3) CHECK (did > 100), + name VARCHAR(40) + ); + </ProgramListing> + + <PARA> + Define a CHECK table constraint + </PARA> + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(3), + name VARCHAR(40) + CONSTRAINT con1 CHECK (did > 100 AND name > '') + ); + </ProgramListing> + + <PARA> + Define a PRIMARY KEY table constraint for the table films. + PRIMARY KEY table constraints can be defined on one or more + columns of the table + </PARA> + <ProgramListing> + CREATE TABLE films ( + code CHAR(05), + title VARCHAR(40), + did DECIMAL(03), + date_prod DATE, + kind CHAR(10), + len INTERVAL HOUR TO MINUTE, + CONSTRAINT code_title PRIMARY KEY(code,title) + ); + </ProgramListing> + + <PARA> + Defines a PRIMARY KEY column constraint for table distributors. + PRIMARY KEY column constraints can only be defined on one column + of the table (the following two examples are equivalents) + </PARA> + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(03), + name CHAR VARYING(40), + PRIMARY KEY(did) + ); + + CREATE TABLE distributors ( + did DECIMAL(03) PRIMARY KEY, + name VARCHAR(40) + ); + </ProgramListing> + <para> + To assign a sequence as the default for the column did, + and a literal to the column name + </PARA> + + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(3) DEFAULT NEXTVAL('serial'), + name VARCHAR(40) DEFAULT 'luso films' + ); + </ProgramListing> + + <REFSECT2 ID="R2-SQL-CREATETABLE-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + CREATE TABLE/INHERITS is a PostgreSQL language extension. + </PARA> + </refsect2> + + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CREATETABLE-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-CREATETABLE-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + In addition to normal CREATE TABLE, SQL92 also supports a + CREATE TEMPORARY TABLE statement. + </PARA> + <synopsis> + CREATE [ {GLOBAL | LOCAL} ] TEMPORARY TABLE table ( + column type [DEFAULT value] [CONSTRAINT column_constraint] [, ...] ) + [CONSTRAINT table_constraint ] + [ ON COMMIT {DELETE | PRESERVE} ROWS ] + </synopsis> + <para> + For temporary tables, the CREATE TEMPORARY TABLE statement + names a new table and defines the table's columns and + constraints. + </para> + <para> + The optional ON COMMIT clause of CREATE TEMPORARY TABLE + specifies whether or not the temporary table should be emptied of + rows whenever COMMIT is executed. If the ON COMMIT clause is + omitted, the default option, ON COMMIT DELETE ROWS, is assumed. + </para> + <para> + To create a temporary table: + </para> + <programlisting> + CREATE TEMPORARY TABLE actors ( + id DECIMAL(03), + name VARCHAR(40), + CONSTRAINT actor_id CHECK (id < 150) + ) ON COMMIT DELETE ROWS + </programlisting> + <para> + TIP: In the current release of PostgreSQL (6.3.2), to create a temporary + table you must create and drop the table by explicit commands. + </para> + + <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-1"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + UNIQUE clause + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for UNIQUE: + Table Constraint definition + </PARA> + <synopsis> + [ CONSTRAINT name ] + UNIQUE ( column [, ...] ) + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + <para> + Column Constraint definition + </para> + <synopsis> + [ CONSTRAINT name ] + UNIQUE + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + </refsect3> + + <REFSECT3 ID="R3-SQL-NOTNULL-4"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + NOT NULL clause + </TITLE> + <PARA> + + SQL92 specifies some additional capabilities for NOT NULL: + </PARA> + <synopsis> + [ CONSTRAINT name ] NOT NULL + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + </REFSECT3> + + <REFSECT3 ID="R3-SQL-DEFAULTCLAUSE-1"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + DEFAULT clause + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for the DEFAULT clause. + A DEFAULT clause is used to set the default value for a column + or a domain. + </para> + <synopsis> + DEFAULT <replaceable class="parameter">literal</replaceable> | + niladic USER function | + niladic datetime function | + NULL + </synopsis> + </refsect3> + + <REFSECT3 ID="R3-SQL-CONSTRAINT-3"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + CONSTRAINT clause + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for CONSTRAINTs, + it also defines assertions and domain constraints. + </PARA> + <PARA> + An assertion is a special type of integrity constraint and share + the same namespace as other constraints. + However, an assertion is not necessarily dependent on one + particular base table as constraints are, so SQL-92 provides the + CREATE ASSERTION statement as an alternate method for defining a + constraint: + </para> + <synopsis> + CREATE ASSERTION name CHECK ( condition ) + </synopsis> + + <PARA> + Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN + statements: + </para> + <PARA> + Domain constraint: + </para> + <synopsis> + [ CONSTRAINT name ] + CHECK constraint + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + <para> + Table constraint definition: + </para> + <synopsis> + [ CONSTRAINT name ] + { PRIMARY KEY constraint | + FOREIGN KEY constraint | + UNIQUE constraint | + CHECK constraint } + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ]</synopsis> + <para> + Column constraint definition: + </para> + <synopsis> + [ CONSTRAINT name ] + { NOT NULL constraint | + PRIMARY KEY constraint | + FOREIGN KEY constraint | + UNIQUE constraint | + CHECK constraint } + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + <para> + A CONSTRAINT definition may contain one deferment attribute + clause and/or one initial constraint mode clause, in any order. + <variablelist> + <varlistentry> + <term>NOT DEFERRABLE</term> + <listitem> + <para> + means that the Constraint must be checked for + violation of its rule after the execution of every SQL statement. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>DEFERRABLE</term> + <listitem> + <para> + means that checking of the Constraint may be deferred + until some later time, but no later than the end of the current + transaction. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + <para> + The constraint mode for every Constraint always has an initial + default value which is set for that Constraint at the beginning + of a transaction. + <variablelist> + <varlistentry> + <term>INITIALLY IMMEDIATE</term> + <listitem> + <para> + means that, as of the start of the transaction, + the Constraint must be checked for violation of its rule after the + execution of every SQL statement. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>INITIALLY DEFERRED</term> + <listitem> + <para> + means that, as of the start of the transaction, + checking of the Constraint may be deferred until some later time, + but no later than the end of the current transaction.</para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect3> + + + <REFSECT3 ID="R3-SQL-CHECK-4"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + CHECK clause + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for CHECK: + </PARA> + <para> + A CHECK constraint is either a table constraint, a column + constraint or a domain constraint. + </para> + <para> + table constraint definition: + </para> + <synopsis> + [ CONSTRAINT name ] + CHECK ( VALUE condition ) + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + <para> + column constraint definition: + </para> + <synopsis> + [ CONSTRAINT name ] + CHECK ( VALUE condition ) + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + <para> + domain constraint definition: + </para> + <synopsis> + [ CONSTRAINT name ] + CHECK ( VALUE condition ) + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + <para> + CHECK domain constraints can be defined in either + a CREATE DOMAIN statement or an ALTER DOMAIN statement: + </para> + <programlisting> + CREATE DOMAIN duration AS SMALLINT + CONSTRAINT minutes CHECK (VALUE IN (90,120,180,240)); + + ALTER DOMAIN cities + ADD CONSTRAINT new_city CHECK (VALUE LIKE 'L%'); + </programlisting> + </refsect3> + + + <REFSECT3 ID="R3-SQL-PRIMARYKEY-1"> + <REFSECT3INFO> + <DATE>1998-04-15</DATE> + </REFSECT3INFO> + <TITLE> + PRIMARY KEY clause + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for PRIMARY KEY: + </para> + <PARA> + Table Constraint definition: + </para> + <synopsis> + [ CONSTRAINT name ] + PRIMARY KEY ( column [, ...] ) + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + <para> + Column Constraint definition: + </para> + <synopsis> + [ CONSTRAINT name ] + PRIMARY KEY + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] + </synopsis> + </refsect3> + </refsect2> + </refsect1> +</refentry> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +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: +--> |