diff options
Diffstat (limited to 'doc/src/sgml/dml.sgml')
| -rw-r--r-- | doc/src/sgml/dml.sgml | 350 |
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 > 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 <= 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> |