1<!-- doc/src/sgml/dml.sgml --> 2 3<chapter id="dml"> 4 <title>Data Manipulation</title> 5 6 <para> 7 The previous chapter discussed how to create tables and other 8 structures to hold your data. Now it is time to fill the tables 9 with data. This chapter covers how to insert, update, and delete 10 table data. The chapter 11 after this will finally explain how to extract your long-lost data 12 from the database. 13 </para> 14 15 <sect1 id="dml-insert"> 16 <title>Inserting Data</title> 17 18 <indexterm zone="dml-insert"> 19 <primary>inserting</primary> 20 </indexterm> 21 22 <indexterm zone="dml-insert"> 23 <primary>INSERT</primary> 24 </indexterm> 25 26 <para> 27 When a table is created, it contains no data. The first thing to 28 do before a database can be of much use is to insert data. Data is 29 conceptually inserted one row at a time. Of course you can also 30 insert more than one row, but there is no way to insert less than 31 one row. Even if you know only some column values, a 32 complete row must be created. 33 </para> 34 35 <para> 36 To create a new row, use the <xref linkend="sql-insert"/> 37 command. The command requires the 38 table name and column values. For 39 example, consider the products table from <xref linkend="ddl"/>: 40<programlisting> 41CREATE TABLE products ( 42 product_no integer, 43 name text, 44 price numeric 45); 46</programlisting> 47 An example command to insert a row would be: 48<programlisting> 49INSERT INTO products VALUES (1, 'Cheese', 9.99); 50</programlisting> 51 The data values are listed in the order in which the columns appear 52 in the table, separated by commas. Usually, the data values will 53 be literals (constants), but scalar expressions are also allowed. 54 </para> 55 56 <para> 57 The above syntax has the drawback that you need to know the order 58 of the columns in the table. To avoid this you can also list the 59 columns explicitly. For example, both of the following commands 60 have the same effect as the one above: 61<programlisting> 62INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99); 63INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1); 64</programlisting> 65 Many users consider it good practice to always list the column 66 names. 67 </para> 68 69 <para> 70 If you don't have values for all the columns, you can omit some of 71 them. In that case, the columns will be filled with their default 72 values. For example: 73<programlisting> 74INSERT INTO products (product_no, name) VALUES (1, 'Cheese'); 75INSERT INTO products VALUES (1, 'Cheese'); 76</programlisting> 77 The second form is a <productname>PostgreSQL</productname> 78 extension. It fills the columns from the left with as many values 79 as are given, and the rest will be defaulted. 80 </para> 81 82 <para> 83 For clarity, you can also request default values explicitly, for 84 individual columns or for the entire row: 85<programlisting> 86INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT); 87INSERT INTO products DEFAULT VALUES; 88</programlisting> 89 </para> 90 91 <para> 92 You can insert multiple rows in a single command: 93<programlisting> 94INSERT INTO products (product_no, name, price) VALUES 95 (1, 'Cheese', 9.99), 96 (2, 'Bread', 1.99), 97 (3, 'Milk', 2.99); 98</programlisting> 99 </para> 100 101 <para> 102 It is also possible to insert the result of a query (which might be no 103 rows, one row, or many rows): 104<programlisting> 105INSERT INTO products (product_no, name, price) 106 SELECT product_no, name, price FROM new_products 107 WHERE release_date = 'today'; 108</programlisting> 109 This provides the full power of the SQL query mechanism (<xref 110 linkend="queries"/>) for computing the rows to be inserted. 111 </para> 112 113 <tip> 114 <para> 115 When inserting a lot of data at the same time, consider using 116 the <xref linkend="sql-copy"/> command. 117 It is not as flexible as the <xref linkend="sql-insert"/> 118 command, but is more efficient. Refer 119 to <xref linkend="populate"/> for more information on improving 120 bulk loading performance. 121 </para> 122 </tip> 123 </sect1> 124 125 <sect1 id="dml-update"> 126 <title>Updating Data</title> 127 128 <indexterm zone="dml-update"> 129 <primary>updating</primary> 130 </indexterm> 131 132 <indexterm zone="dml-update"> 133 <primary>UPDATE</primary> 134 </indexterm> 135 136 <para> 137 The modification of data that is already in the database is 138 referred to as updating. You can update individual rows, all the 139 rows in a table, or a subset of all rows. Each column can be 140 updated separately; the other columns are not affected. 141 </para> 142 143 <para> 144 To update existing rows, use the <xref linkend="sql-update"/> 145 command. This requires 146 three pieces of information: 147 <orderedlist spacing="compact"> 148 <listitem> 149 <para>The name of the table and column to update</para> 150 </listitem> 151 152 <listitem> 153 <para>The new value of the column</para> 154 </listitem> 155 156 <listitem> 157 <para>Which row(s) to update</para> 158 </listitem> 159 </orderedlist> 160 </para> 161 162 <para> 163 Recall from <xref linkend="ddl"/> that SQL does not, in general, 164 provide a unique identifier for rows. Therefore it is not 165 always possible to directly specify which row to update. 166 Instead, you specify which conditions a row must meet in order to 167 be updated. Only if you have a primary key in the table (independent of 168 whether you declared it or not) can you reliably address individual rows 169 by choosing a condition that matches the primary key. 170 Graphical database access tools rely on this fact to allow you to 171 update rows individually. 172 </para> 173 174 <para> 175 For example, this command updates all products that have a price of 176 5 to have a price of 10: 177<programlisting> 178UPDATE products SET price = 10 WHERE price = 5; 179</programlisting> 180 This might cause zero, one, or many rows to be updated. It is not 181 an error to attempt an update that does not match any rows. 182 </para> 183 184 <para> 185 Let's look at that command in detail. First is the key word 186 <literal>UPDATE</literal> followed by the table name. As usual, 187 the table name can be schema-qualified, otherwise it is looked up 188 in the path. Next is the key word <literal>SET</literal> followed 189 by the column name, an equal sign, and the new column value. The 190 new column value can be any scalar expression, not just a constant. 191 For example, if you want to raise the price of all products by 10% 192 you could use: 193<programlisting> 194UPDATE products SET price = price * 1.10; 195</programlisting> 196 As you see, the expression for the new value can refer to the existing 197 value(s) in the row. We also left out the <literal>WHERE</literal> clause. 198 If it is omitted, it means that all rows in the table are updated. 199 If it is present, only those rows that match the 200 <literal>WHERE</literal> condition are updated. Note that the equals 201 sign in the <literal>SET</literal> clause is an assignment while 202 the one in the <literal>WHERE</literal> clause is a comparison, but 203 this does not create any ambiguity. Of course, the 204 <literal>WHERE</literal> condition does 205 not have to be an equality test. Many other operators are 206 available (see <xref linkend="functions"/>). But the expression 207 needs to evaluate to a Boolean result. 208 </para> 209 210 <para> 211 You can update more than one column in an 212 <command>UPDATE</command> command by listing more than one 213 assignment in the <literal>SET</literal> clause. For example: 214<programlisting> 215UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0; 216</programlisting> 217 </para> 218 </sect1> 219 220 <sect1 id="dml-delete"> 221 <title>Deleting Data</title> 222 223 <indexterm zone="dml-delete"> 224 <primary>deleting</primary> 225 </indexterm> 226 227 <indexterm zone="dml-delete"> 228 <primary>DELETE</primary> 229 </indexterm> 230 231 <para> 232 So far we have explained how to add data to tables and how to 233 change data. What remains is to discuss how to remove data that is 234 no longer needed. Just as adding data is only possible in whole 235 rows, you can only remove entire rows from a table. In the 236 previous section we explained that SQL does not provide a way to 237 directly address individual rows. Therefore, removing rows can 238 only be done by specifying conditions that the rows to be removed 239 have to match. If you have a primary key in the table then you can 240 specify the exact row. But you can also remove groups of rows 241 matching a condition, or you can remove all rows in the table at 242 once. 243 </para> 244 245 <para> 246 You use the <xref linkend="sql-delete"/> 247 command to remove rows; the syntax is very similar to the 248 <command>UPDATE</command> command. For instance, to remove all 249 rows from the products table that have a price of 10, use: 250<programlisting> 251DELETE FROM products WHERE price = 10; 252</programlisting> 253 </para> 254 255 <para> 256 If you simply write: 257<programlisting> 258DELETE FROM products; 259</programlisting> 260 then all rows in the table will be deleted! Caveat programmer. 261 </para> 262 </sect1> 263 264 <sect1 id="dml-returning"> 265 <title>Returning Data from Modified Rows</title> 266 267 <indexterm zone="dml-returning"> 268 <primary>RETURNING</primary> 269 </indexterm> 270 271 <indexterm zone="dml-returning"> 272 <primary>INSERT</primary> 273 <secondary>RETURNING</secondary> 274 </indexterm> 275 276 <indexterm zone="dml-returning"> 277 <primary>UPDATE</primary> 278 <secondary>RETURNING</secondary> 279 </indexterm> 280 281 <indexterm zone="dml-returning"> 282 <primary>DELETE</primary> 283 <secondary>RETURNING</secondary> 284 </indexterm> 285 286 <para> 287 Sometimes it is useful to obtain data from modified rows while they are 288 being manipulated. The <command>INSERT</command>, <command>UPDATE</command>, 289 and <command>DELETE</command> commands all have an 290 optional <literal>RETURNING</literal> clause that supports this. Use 291 of <literal>RETURNING</literal> avoids performing an extra database query to 292 collect the data, and is especially valuable when it would otherwise be 293 difficult to identify the modified rows reliably. 294 </para> 295 296 <para> 297 The allowed contents of a <literal>RETURNING</literal> clause are the same as 298 a <command>SELECT</command> command's output list 299 (see <xref linkend="queries-select-lists"/>). It can contain column 300 names of the command's target table, or value expressions using those 301 columns. A common shorthand is <literal>RETURNING *</literal>, which selects 302 all columns of the target table in order. 303 </para> 304 305 <para> 306 In an <command>INSERT</command>, the data available to <literal>RETURNING</literal> is 307 the row as it was inserted. This is not so useful in trivial inserts, 308 since it would just repeat the data provided by the client. But it can 309 be very handy when relying on computed default values. For example, 310 when using a <link linkend="datatype-serial"><type>serial</type></link> 311 column to provide unique identifiers, <literal>RETURNING</literal> can return 312 the ID assigned to a new row: 313<programlisting> 314CREATE TABLE users (firstname text, lastname text, id serial primary key); 315 316INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id; 317</programlisting> 318 The <literal>RETURNING</literal> clause is also very useful 319 with <literal>INSERT ... SELECT</literal>. 320 </para> 321 322 <para> 323 In an <command>UPDATE</command>, the data available to <literal>RETURNING</literal> is 324 the new content of the modified row. For example: 325<programlisting> 326UPDATE products SET price = price * 1.10 327 WHERE price <= 99.99 328 RETURNING name, price AS new_price; 329</programlisting> 330 </para> 331 332 <para> 333 In a <command>DELETE</command>, the data available to <literal>RETURNING</literal> is 334 the content of the deleted row. For example: 335<programlisting> 336DELETE FROM products 337 WHERE obsoletion_date = 'today' 338 RETURNING *; 339</programlisting> 340 </para> 341 342 <para> 343 If there are triggers (<xref linkend="triggers"/>) on the target table, 344 the data available to <literal>RETURNING</literal> is the row as modified by 345 the triggers. Thus, inspecting columns computed by triggers is another 346 common use-case for <literal>RETURNING</literal>. 347 </para> 348 349 </sect1> 350</chapter> 351