diff options
Diffstat (limited to 'doc/src/sgml/ref/insert.sgml')
| -rw-r--r-- | doc/src/sgml/ref/insert.sgml | 384 |
1 files changed, 202 insertions, 182 deletions
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 2d90cc65fe0..b5e2c0241ea 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -12,188 +12,208 @@ INSERT <REFPURPOSE> Inserts new rows into a table </REFPURPOSE> -<REFSYNOPSISDIV> -<REFSYNOPSISDIVINFO> -<DATE>1998-09-23</DATE> -</REFSYNOPSISDIVINFO> -<SYNOPSIS> -INSERT INTO <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> [ ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) ] - { VALUES ( <REPLACEABLE CLASS="PARAMETER">expression</REPLACEABLE> [, ...] ) | SELECT <REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE> } -</SYNOPSIS> - -<REFSECT2 ID="R2-SQL-INSERT-1"> -<REFSECT2INFO> -<DATE>1998-09-23</DATE> -</REFSECT2INFO> -<TITLE> -Inputs -</TITLE> -<PARA> -</PARA> -<VARIABLELIST> -<VARLISTENTRY> -<TERM> -<REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -The name of an existing table. - -<VARLISTENTRY> -<TERM> -<REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -The name of a column in <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>. - -<VARLISTENTRY> -<TERM> -<REPLACEABLE CLASS="PARAMETER">expression</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -A valid expression or value to assign to <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>. - -<VARLISTENTRY> -<TERM> -<REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -A valid query. Refer to the SELECT statement for a further description - of valid arguments. - -</VARIABLELIST> - -</REFSECT2> - -<REFSECT2 ID="R2-SQL-INSERT-2"> -<REFSECT2INFO> -<DATE>1998-09-23</DATE> -</REFSECT2INFO> -<TITLE> -Outputs -</TITLE> -<PARA> - -<VARIABLELIST> -<VARLISTENTRY> -<TERM> -<ReturnValue>INSERT <replaceable>oid</replaceable></ReturnValue> 1 -</TERM> -<LISTITEM> -<PARA> -Message returned if only one row was inserted. -<ReturnValue><replaceable>oid</replaceable></ReturnValue> - is the row identifier. - -<VARLISTENTRY> -<TERM> -<ReturnValue>INSERT 0 <replaceable>#</replaceable></ReturnValue> -</TERM> -<LISTITEM> -<PARA> -Message returned if more than one rows were inserted. -<ReturnValue><replaceable>#</replaceable></ReturnValue> - is the number of rows inserted. - -</VARIABLELIST> - -</REFSECT2> -</REFSYNOPSISDIV> - -<REFSECT1 ID="R1-SQL-INSERT-1"> -<REFSECT1INFO> -<DATE>1998-09-02</DATE> -</REFSECT1INFO> -<TITLE> -Description -</TITLE> -<PARA> - INSERT allows one to insert new rows into a table. One can insert - a single row at time or several rows as a result of a query. - The columns in the target list may be listed in any order. - In every column not present in the target list will be inserted - the default value, if column has not a declared default value - it will be assumed as NULL. If the expression for each column - is not of the correct data type, automatic type coercion will be - attempted. - -<para> - You must have insert privilege to a table in order to append - to it, as well as select privilege on any table specified - in a WHERE clause. - -<REFSECT1 ID="R1-SQL-INSERT-2"> -<TITLE> -Usage -</TITLE> -<PARA> -<ProgramListing> ---Insert a single row into table films; ---(in the second example the column date_prod is omitted ---therefore will be stored in it a default value of NULL): --- -INSERT INTO films VALUES - ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute'); - -INSERT INTO films (code, title, did, date_prod, kind) - VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama'); -</ProgramListing> - -<ProgramListing> ---Insert a single row into table distributors, note that ---only column "name" is specified, to the non specified ---column "did" will be assigned its default value: --- -INSERT INTO distributors (name) VALUES ('British Lion'); -</ProgramListing> - -<ProgramListing> ---Insert several rows into table films from table tmp: --- -INSERT INTO films - SELECT * FROM tmp; -</ProgramListing> - -<ProgramListing> ---Insert into arrays: ---Create an empty 3x3 gameboard for noughts-and-crosses ---(all of these queries create the same board attribute) ---(Refer to the <citetitle>PostgreSQL User's Guide</citetitle> for further ---information about arrays). - -INSERT INTO tictactoe (game, board[1:3][1:3]) - VALUES (1,'{{"","",""},{},{"",""}}'); -INSERT INTO tictactoe (game, board[3][3]) - VALUES (2,'{}'); -INSERT INTO tictactoe (game, board) - VALUES (3,'{{,,},{,,},{,,}}'); -</ProgramListing> - -</REFSECT1> - -<REFSECT1 ID="R1-SQL-INSERT-3"> -<TITLE> -Compatibility -</TITLE> -<PARA> -</PARA> - -<REFSECT2 ID="R2-SQL-INSERT-4"> -<REFSECT2INFO> -<DATE>1998-09-23</DATE> -</REFSECT2INFO> -<TITLE> -SQL92 -</TITLE> -<PARA> -The INSERT statement is fully compatible with <acronym>SQL92</acronym>. -Possible limitations in features of the -<REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE> -clause are documented for the SELECT statement. - + </refnamediv> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-09-23</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + INSERT INTO <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> [ ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) ] + { VALUES ( <REPLACEABLE CLASS="PARAMETER">expression</REPLACEABLE> [, ...] ) | SELECT <REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE> } + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-INSERT-1"> + <REFSECT2INFO> + <DATE>1998-09-23</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + The name of an existing table. + </para> + </listitem> + </varlistentry> + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + The name of a column in <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>. + </para> + </listitem> + </varlistentry> + + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">expression</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + A valid expression or value to assign to <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>. + </para> + </listitem> + </varlistentry> + + <VARLISTENTRY> + <TERM> + <REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + A valid query. Refer to the SELECT statement for a further description + of valid arguments. + </para> + </listitem> + </varlistentry> + + </VARIABLELIST> + + </REFSECT2> + + <REFSECT2 ID="R2-SQL-INSERT-2"> + <REFSECT2INFO> + <DATE>1998-09-23</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>INSERT <replaceable>oid</replaceable></ReturnValue> 1 + </TERM> + <LISTITEM> + <PARA> + Message returned if only one row was inserted. + <ReturnValue><replaceable>oid</replaceable></ReturnValue> + is the numeric <acronym>OID</acronym> of the inserted row. + </para> + </listitem> + </varlistentry> + <VARLISTENTRY> + <TERM> + <ReturnValue>INSERT 0 <replaceable>#</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if more than one rows were inserted. + <ReturnValue><replaceable>#</replaceable></ReturnValue> + is the number of rows inserted. + </para> + </listitem> + </varlistentry> + </VARIABLELIST> + </para> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-INSERT-1"> + <REFSECT1INFO> + <DATE>1998-09-02</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + INSERT allows one to insert new rows into a table. One can insert + a single row at time or several rows as a result of a query. + The columns in the target list may be listed in any order. + In every column not present in the target list will be inserted + the default value, if column has not a declared default value + it will be assumed as NULL. If the expression for each column + is not of the correct data type, automatic type coercion will be + attempted. + </para> + <para> + You must have insert privilege to a table in order to append + to it, as well as select privilege on any table specified + in a WHERE clause. + </para> + </refsect1> + + <REFSECT1 ID="R1-SQL-INSERT-2"> + <TITLE> + Usage + </TITLE> + <PARA> + <ProgramListing> + --Insert a single row into table films; + --(in the second example the column date_prod is omitted + --therefore will be stored in it a default value of NULL): + -- + INSERT INTO films VALUES + ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute'); + + INSERT INTO films (code, title, did, date_prod, kind) + VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama'); + </ProgramListing> + + <ProgramListing> + --Insert a single row into table distributors, note that + --only column "name" is specified, to the non specified + --column "did" will be assigned its default value: + -- + INSERT INTO distributors (name) VALUES ('British Lion'); + </ProgramListing> + + <ProgramListing> + --Insert several rows into table films from table tmp: + -- + INSERT INTO films + SELECT * FROM tmp; + </ProgramListing> + + <ProgramListing> + --Insert into arrays: + --Create an empty 3x3 gameboard for noughts-and-crosses + --(all of these queries create the same board attribute) + --(Refer to the <citetitle>PostgreSQL User's Guide</citetitle> for further + --information about arrays). + + INSERT INTO tictactoe (game, board[1:3][1:3]) + VALUES (1,'{{"","",""},{},{"",""}}'); + INSERT INTO tictactoe (game, board[3][3]) + VALUES (2,'{}'); + INSERT INTO tictactoe (game, board) + VALUES (3,'{{,,},{,,},{,,}}'); + </ProgramListing> + </para> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-INSERT-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-INSERT-4"> + <REFSECT2INFO> + <DATE>1998-09-23</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + The INSERT statement is fully compatible with <acronym>SQL92</acronym>. + Possible limitations in features of the + <REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE> + clause are documented for the SELECT statement. + </para> + </refsect2> + </refsect1> </REFENTRY> <!-- |