Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 350 insertions, 0 deletions

diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml
new file mode 100644
index 0000000..cbbc5e2
--- /dev/null
+++ b/doc/src/sgml/dml.sgml
@@ -0,0 +1,350 @@
+<!-- doc/src/sgml/dml.sgml -->
+
+<chapter id="dml">
+ <title>Data Manipulation</title>
+
+ <para>
+  The previous chapter discussed how to create tables and other
+  structures to hold your data.  Now it is time to fill the tables
+  with data.  This chapter covers how to insert, update, and delete
+  table data.  The chapter
+  after this will finally explain how to extract your long-lost data
+  from the database.
+ </para>
+
+ <sect1 id="dml-insert">
+  <title>Inserting Data</title>
+
+  <indexterm zone="dml-insert">
+   <primary>inserting</primary>
+  </indexterm>
+
+  <indexterm zone="dml-insert">
+   <primary>INSERT</primary>
+  </indexterm>
+
+  <para>
+   When a table is created, it contains no data.  The first thing to
+   do before a database can be of much use is to insert data.  Data is
+   inserted one row at a time.  You can also insert more than one row
+   in a single command, but it is not possible to insert something that
+   is not a complete row.  Even if you know only some column values, a
+   complete row must be created.
+  </para>
+
+  <para>
+   To create a new row, use the <xref linkend="sql-insert"/>
+   command.  The command requires the
+   table name and column values.  For
+   example, consider the products table from <xref linkend="ddl"/>:
+<programlisting>
+CREATE TABLE products (
+    product_no integer,
+    name text,
+    price numeric
+);
+</programlisting>
+   An example command to insert a row would be:
+<programlisting>
+INSERT INTO products VALUES (1, 'Cheese', 9.99);
+</programlisting>
+   The data values are listed in the order in which the columns appear
+   in the table, separated by commas.  Usually, the data values will
+   be literals (constants), but scalar expressions are also allowed.
+  </para>
+
+  <para>
+   The above syntax has the drawback that you need to know the order
+   of the columns in the table.  To avoid this you can also list the
+   columns explicitly.  For example, both of the following commands
+   have the same effect as the one above:
+<programlisting>
+INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
+INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
+</programlisting>
+   Many users consider it good practice to always list the column
+   names.
+  </para>
+
+  <para>
+   If you don't have values for all the columns, you can omit some of
+   them.  In that case, the columns will be filled with their default
+   values.  For example:
+<programlisting>
+INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
+INSERT INTO products VALUES (1, 'Cheese');
+</programlisting>
+   The second form is a <productname>PostgreSQL</productname>
+   extension.  It fills the columns from the left with as many values
+   as are given, and the rest will be defaulted.
+  </para>
+
+  <para>
+   For clarity, you can also request default values explicitly, for
+   individual columns or for the entire row:
+<programlisting>
+INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
+INSERT INTO products DEFAULT VALUES;
+</programlisting>
+  </para>
+
+  <para>
+   You can insert multiple rows in a single command:
+<programlisting>
+INSERT INTO products (product_no, name, price) VALUES
+    (1, 'Cheese', 9.99),
+    (2, 'Bread', 1.99),
+    (3, 'Milk', 2.99);
+</programlisting>
+  </para>
+
+  <para>
+   It is also possible to insert the result of a query (which might be no
+   rows, one row, or many rows):
+<programlisting>
+INSERT INTO products (product_no, name, price)
+  SELECT product_no, name, price FROM new_products
+    WHERE release_date = 'today';
+</programlisting>
+   This provides the full power of the SQL query mechanism (<xref
+   linkend="queries"/>) for computing the rows to be inserted.
+  </para>
+
+  <tip>
+   <para>
+    When inserting a lot of data at the same time, consider using
+    the <xref linkend="sql-copy"/> command.
+    It is not as flexible as the <xref linkend="sql-insert"/>
+    command, but is more efficient. Refer
+    to <xref linkend="populate"/> for more information on improving
+    bulk loading performance.
+   </para>
+  </tip>
+ </sect1>
+
+ <sect1 id="dml-update">
+  <title>Updating Data</title>
+
+  <indexterm zone="dml-update">
+   <primary>updating</primary>
+  </indexterm>
+
+  <indexterm zone="dml-update">
+   <primary>UPDATE</primary>
+  </indexterm>
+
+  <para>
+   The modification of data that is already in the database is
+   referred to as updating.  You can update individual rows, all the
+   rows in a table, or a subset of all rows.  Each column can be
+   updated separately; the other columns are not affected.
+  </para>
+
+  <para>
+   To update existing rows, use the <xref linkend="sql-update"/>
+   command.  This requires
+   three pieces of information:
+   <orderedlist spacing="compact">
+    <listitem>
+     <para>The name of the table and column to update</para>
+    </listitem>
+
+    <listitem>
+     <para>The new value of the column</para>
+    </listitem>
+
+    <listitem>
+     <para>Which row(s) to update</para>
+    </listitem>
+   </orderedlist>
+  </para>
+
+  <para>
+   Recall from <xref linkend="ddl"/> that SQL does not, in general,
+   provide a unique identifier for rows.  Therefore it is not
+   always possible to directly specify which row to update.
+   Instead, you specify which conditions a row must meet in order to
+   be updated.  Only if you have a primary key in the table (independent of
+   whether you declared it or not) can you reliably address individual rows
+   by choosing a condition that matches the primary key.
+   Graphical database access tools rely on this fact to allow you to
+   update rows individually.
+  </para>
+
+  <para>
+   For example, this command updates all products that have a price of
+   5 to have a price of 10:
+<programlisting>
+UPDATE products SET price = 10 WHERE price = 5;
+</programlisting>
+    This might cause zero, one, or many rows to be updated.  It is not
+    an error to attempt an update that does not match any rows.
+  </para>
+
+  <para>
+   Let's look at that command in detail. First is the key word
+   <literal>UPDATE</literal> followed by the table name.  As usual,
+   the table name can be schema-qualified, otherwise it is looked up
+   in the path.  Next is the key word <literal>SET</literal> followed
+   by the column name, an equal sign, and the new column value.  The
+   new column value can be any scalar expression, not just a constant.
+   For example, if you want to raise the price of all products by 10%
+   you could use:
+<programlisting>
+UPDATE products SET price = price * 1.10;
+</programlisting>
+   As you see, the expression for the new value can refer to the existing
+   value(s) in the row.  We also left out the <literal>WHERE</literal> clause.
+   If it is omitted, it means that all rows in the table are updated.
+   If it is present, only those rows that match the
+   <literal>WHERE</literal> condition are updated.  Note that the equals
+   sign in the <literal>SET</literal> clause is an assignment while
+   the one in the <literal>WHERE</literal> clause is a comparison, but
+   this does not create any ambiguity.  Of course, the
+   <literal>WHERE</literal> condition does
+   not have to be an equality test.  Many other operators are
+   available (see <xref linkend="functions"/>).  But the expression
+   needs to evaluate to a Boolean result.
+  </para>
+
+  <para>
+   You can update more than one column in an
+   <command>UPDATE</command> command by listing more than one
+   assignment in the <literal>SET</literal> clause.  For example:
+<programlisting>
+UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a &gt; 0;
+</programlisting>
+  </para>
+ </sect1>
+
+ <sect1 id="dml-delete">
+  <title>Deleting Data</title>
+
+  <indexterm zone="dml-delete">
+   <primary>deleting</primary>
+  </indexterm>
+
+  <indexterm zone="dml-delete">
+   <primary>DELETE</primary>
+  </indexterm>
+
+  <para>
+   So far we have explained how to add data to tables and how to
+   change data.  What remains is to discuss how to remove data that is
+   no longer needed.  Just as adding data is only possible in whole
+   rows, you can only remove entire rows from a table.  In the
+   previous section we explained that SQL does not provide a way to
+   directly address individual rows.  Therefore, removing rows can
+   only be done by specifying conditions that the rows to be removed
+   have to match.  If you have a primary key in the table then you can
+   specify the exact row.  But you can also remove groups of rows
+   matching a condition, or you can remove all rows in the table at
+   once.
+  </para>
+
+  <para>
+   You use the <xref linkend="sql-delete"/>
+   command to remove rows; the syntax is very similar to the
+   <xref linkend="sql-update"/> command.  For instance, to remove all
+   rows from the products table that have a price of 10, use:
+<programlisting>
+DELETE FROM products WHERE price = 10;
+</programlisting>
+  </para>
+
+  <para>
+   If you simply write:
+<programlisting>
+DELETE FROM products;
+</programlisting>
+   then all rows in the table will be deleted!  Caveat programmer.
+  </para>
+ </sect1>
+
+ <sect1 id="dml-returning">
+  <title>Returning Data from Modified Rows</title>
+
+  <indexterm zone="dml-returning">
+   <primary>RETURNING</primary>
+  </indexterm>
+
+  <indexterm zone="dml-returning">
+   <primary>INSERT</primary>
+   <secondary>RETURNING</secondary>
+  </indexterm>
+
+  <indexterm zone="dml-returning">
+   <primary>UPDATE</primary>
+   <secondary>RETURNING</secondary>
+  </indexterm>
+
+  <indexterm zone="dml-returning">
+   <primary>DELETE</primary>
+   <secondary>RETURNING</secondary>
+  </indexterm>
+
+  <para>
+   Sometimes it is useful to obtain data from modified rows while they are
+   being manipulated.  The <command>INSERT</command>, <command>UPDATE</command>,
+   and <command>DELETE</command> commands all have an
+   optional <literal>RETURNING</literal> clause that supports this.  Use
+   of <literal>RETURNING</literal> avoids performing an extra database query to
+   collect the data, and is especially valuable when it would otherwise be
+   difficult to identify the modified rows reliably.
+  </para>
+
+  <para>
+   The allowed contents of a <literal>RETURNING</literal> clause are the same as
+   a <command>SELECT</command> command's output list
+   (see <xref linkend="queries-select-lists"/>).  It can contain column
+   names of the command's target table, or value expressions using those
+   columns.  A common shorthand is <literal>RETURNING *</literal>, which selects
+   all columns of the target table in order.
+  </para>
+
+  <para>
+   In an <command>INSERT</command>, the data available to <literal>RETURNING</literal> is
+   the row as it was inserted.  This is not so useful in trivial inserts,
+   since it would just repeat the data provided by the client.  But it can
+   be very handy when relying on computed default values.  For example,
+   when using a <link linkend="datatype-serial"><type>serial</type></link>
+   column to provide unique identifiers, <literal>RETURNING</literal> can return
+   the ID assigned to a new row:
+<programlisting>
+CREATE TABLE users (firstname text, lastname text, id serial primary key);
+
+INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
+</programlisting>
+   The <literal>RETURNING</literal> clause is also very useful
+   with <literal>INSERT ... SELECT</literal>.
+  </para>
+
+  <para>
+   In an <command>UPDATE</command>, the data available to <literal>RETURNING</literal> is
+   the new content of the modified row.  For example:
+<programlisting>
+UPDATE products SET price = price * 1.10
+  WHERE price &lt;= 99.99
+  RETURNING name, price AS new_price;
+</programlisting>
+  </para>
+
+  <para>
+   In a <command>DELETE</command>, the data available to <literal>RETURNING</literal> is
+   the content of the deleted row.  For example:
+<programlisting>
+DELETE FROM products
+  WHERE obsoletion_date = 'today'
+  RETURNING *;
+</programlisting>
+  </para>
+
+  <para>
+   If there are triggers (<xref linkend="triggers"/>) on the target table,
+   the data available to <literal>RETURNING</literal> is the row as modified by
+   the triggers.  Thus, inspecting columns computed by triggers is another
+   common use-case for <literal>RETURNING</literal>.
+  </para>
+
+ </sect1>
+</chapter>