| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/update.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-update"> |
| 7 |
<indexterm zone="sql-update"> |
| 8 |
<primary>UPDATE</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>UPDATE</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>UPDATE</refname> |
| 19 |
<refpurpose>update rows of a table</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ] |
| 25 |
UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ] |
| 26 |
SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } | |
| 27 |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | |
| 28 |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> ) |
| 29 |
} [, ...] |
| 30 |
[ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ] |
| 31 |
[ WHERE <replaceable class="parameter">condition</replaceable> | WHERE CURRENT OF <replaceable class="parameter">cursor_name</replaceable> ] |
| 32 |
[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ] |
| 33 |
{ * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] |
| 34 |
</synopsis> |
| 35 |
</refsynopsisdiv> |
| 36 |
|
| 37 |
<refsect1> |
| 38 |
<title>Description</title> |
| 39 |
|
| 40 |
<para> |
| 41 |
<command>UPDATE</command> changes the values of the specified |
| 42 |
columns in all rows that satisfy the condition. Only the columns to |
| 43 |
be modified need be mentioned in the <literal>SET</literal> clause; |
| 44 |
columns not explicitly modified retain their previous values. |
| 45 |
</para> |
| 46 |
|
| 47 |
<para> |
| 48 |
There are two ways to modify a table using information contained in |
| 49 |
other tables in the database: using sub-selects, or specifying |
| 50 |
additional tables in the <literal>FROM</literal> clause. Which |
| 51 |
technique is more appropriate depends on the specific |
| 52 |
circumstances. |
| 53 |
</para> |
| 54 |
|
| 55 |
<para> |
| 56 |
The optional <literal>RETURNING</literal> clause causes <command>UPDATE</command> |
| 57 |
to compute and return value(s) based on each row actually updated. |
| 58 |
Any expression using the table's columns, and/or columns of other |
| 59 |
tables mentioned in <literal>FROM</literal>, can be computed. |
| 60 |
By default, the new (post-update) values of the table's columns are used, |
| 61 |
but it is also possible to request the old (pre-update) values. |
| 62 |
The syntax of the <literal>RETURNING</literal> list is identical to that of the |
| 63 |
output list of <command>SELECT</command>. |
| 64 |
</para> |
| 65 |
|
| 66 |
<para> |
| 67 |
You must have the <literal>UPDATE</literal> privilege on the table, |
| 68 |
or at least on the column(s) that are listed to be updated. |
| 69 |
You must also have the <literal>SELECT</literal> |
| 70 |
privilege on any column whose values are read in the |
| 71 |
<replaceable class="parameter">expressions</replaceable> or |
| 72 |
<replaceable class="parameter">condition</replaceable>. |
| 73 |
</para> |
| 74 |
</refsect1> |
| 75 |
|
| 76 |
<refsect1> |
| 77 |
<title>Parameters</title> |
| 78 |
|
| 79 |
<variablelist> |
| 80 |
<varlistentry> |
| 81 |
<term><replaceable class="parameter">with_query</replaceable></term> |
| 82 |
<listitem> |
| 83 |
<para> |
| 84 |
The <literal>WITH</literal> clause allows you to specify one or more |
| 85 |
subqueries that can be referenced by name in the <command>UPDATE</command> |
| 86 |
query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/> |
| 87 |
for details. |
| 88 |
</para> |
| 89 |
</listitem> |
| 90 |
</varlistentry> |
| 91 |
|
| 92 |
<varlistentry> |
| 93 |
<term><replaceable class="parameter">table_name</replaceable></term> |
| 94 |
<listitem> |
| 95 |
<para> |
| 96 |
The name (optionally schema-qualified) of the table to update. |
| 97 |
If <literal>ONLY</literal> is specified before the table name, matching rows |
| 98 |
are updated in the named table only. If <literal>ONLY</literal> is not |
| 99 |
specified, matching rows are also updated in any tables inheriting from |
| 100 |
the named table. Optionally, <literal>*</literal> can be specified after the |
| 101 |
table name to explicitly indicate that descendant tables are included. |
| 102 |
</para> |
| 103 |
</listitem> |
| 104 |
</varlistentry> |
| 105 |
|
| 106 |
<varlistentry> |
| 107 |
<term><replaceable class="parameter">alias</replaceable></term> |
| 108 |
<listitem> |
| 109 |
<para> |
| 110 |
A substitute name for the target table. When an alias is |
| 111 |
provided, it completely hides the actual name of the table. For |
| 112 |
example, given <literal>UPDATE foo AS f</literal>, the remainder of the |
| 113 |
<command>UPDATE</command> statement must refer to this table as |
| 114 |
<literal>f</literal> not <literal>foo</literal>. |
| 115 |
</para> |
| 116 |
</listitem> |
| 117 |
</varlistentry> |
| 118 |
|
| 119 |
<varlistentry> |
| 120 |
<term><replaceable class="parameter">column_name</replaceable></term> |
| 121 |
<listitem> |
| 122 |
<para> |
| 123 |
The name of a column in the table named by <replaceable |
| 124 |
class="parameter">table_name</replaceable>. |
| 125 |
The column name can be qualified with a subfield name or array |
| 126 |
subscript, if needed. Do not include the table's name in the |
| 127 |
specification of a target column — for example, |
| 128 |
<literal>UPDATE table_name SET table_name.col = 1</literal> is invalid. |
| 129 |
</para> |
| 130 |
</listitem> |
| 131 |
</varlistentry> |
| 132 |
|
| 133 |
<varlistentry> |
| 134 |
<term><replaceable class="parameter">expression</replaceable></term> |
| 135 |
<listitem> |
| 136 |
<para> |
| 137 |
An expression to assign to the column. The expression can use the |
| 138 |
old values of this and other columns in the table. |
| 139 |
</para> |
| 140 |
</listitem> |
| 141 |
</varlistentry> |
| 142 |
|
| 143 |
<varlistentry> |
| 144 |
<term><literal>DEFAULT</literal></term> |
| 145 |
<listitem> |
| 146 |
<para> |
| 147 |
Set the column to its default value (which will be NULL if no specific |
| 148 |
default expression has been assigned to it). An identity column will be |
| 149 |
set to a new value generated by the associated sequence. For a |
| 150 |
generated column, specifying this is permitted but merely specifies the |
| 151 |
normal behavior of computing the column from its generation expression. |
| 152 |
</para> |
| 153 |
</listitem> |
| 154 |
</varlistentry> |
| 155 |
|
| 156 |
<varlistentry> |
| 157 |
<term><replaceable class="parameter">sub-SELECT</replaceable></term> |
| 158 |
<listitem> |
| 159 |
<para> |
| 160 |
A <literal>SELECT</literal> sub-query that produces as many output columns |
| 161 |
as are listed in the parenthesized column list preceding it. The |
| 162 |
sub-query must yield no more than one row when executed. If it |
| 163 |
yields one row, its column values are assigned to the target columns; |
| 164 |
if it yields no rows, NULL values are assigned to the target columns. |
| 165 |
The sub-query can refer to old values of the current row of the table |
| 166 |
being updated. |
| 167 |
</para> |
| 168 |
</listitem> |
| 169 |
</varlistentry> |
| 170 |
|
| 171 |
<varlistentry> |
| 172 |
<term><replaceable class="parameter">from_item</replaceable></term> |
| 173 |
<listitem> |
| 174 |
<para> |
| 175 |
A table expression allowing columns from other tables to appear in |
| 176 |
the <literal>WHERE</literal> condition and update expressions. This |
| 177 |
uses the same syntax as the <link |
| 178 |
linkend="sql-from"><literal>FROM</literal></link> clause of |
| 179 |
a <command>SELECT</command> statement; |
| 180 |
for example, an alias for the table name can be specified. Do not |
| 181 |
repeat the target table as a <replaceable>from_item</replaceable> |
| 182 |
unless you intend a self-join (in which case it must appear with |
| 183 |
an alias in the <replaceable>from_item</replaceable>). |
| 184 |
</para> |
| 185 |
</listitem> |
| 186 |
</varlistentry> |
| 187 |
|
| 188 |
<varlistentry> |
| 189 |
<term><replaceable class="parameter">condition</replaceable></term> |
| 190 |
<listitem> |
| 191 |
<para> |
| 192 |
An expression that returns a value of type <type>boolean</type>. |
| 193 |
Only rows for which this expression returns <literal>true</literal> |
| 194 |
will be updated. |
| 195 |
</para> |
| 196 |
</listitem> |
| 197 |
</varlistentry> |
| 198 |
|
| 199 |
<varlistentry> |
| 200 |
<term><replaceable class="parameter">cursor_name</replaceable></term> |
| 201 |
<listitem> |
| 202 |
<para> |
| 203 |
The name of the cursor to use in a <literal>WHERE CURRENT OF</literal> |
| 204 |
condition. The row to be updated is the one most recently fetched |
| 205 |
from this cursor. The cursor must be a non-grouping |
| 206 |
query on the <command>UPDATE</command>'s target table. |
| 207 |
Note that <literal>WHERE CURRENT OF</literal> cannot be |
| 208 |
specified together with a Boolean condition. See |
| 209 |
<xref linkend="sql-declare"/> |
| 210 |
for more information about using cursors with |
| 211 |
<literal>WHERE CURRENT OF</literal>. |
| 212 |
</para> |
| 213 |
</listitem> |
| 214 |
</varlistentry> |
| 215 |
|
| 216 |
<varlistentry> |
| 217 |
<term><replaceable class="parameter">output_alias</replaceable></term> |
| 218 |
<listitem> |
| 219 |
<para> |
| 220 |
An optional substitute name for <literal>OLD</literal> or |
| 221 |
<literal>NEW</literal> rows in the <literal>RETURNING</literal> list. |
| 222 |
</para> |
| 223 |
|
| 224 |
<para> |
| 225 |
By default, old values from the target table can be returned by writing |
| 226 |
<literal>OLD.<replaceable class="parameter">column_name</replaceable></literal> |
| 227 |
or <literal>OLD.*</literal>, and new values can be returned by writing |
| 228 |
<literal>NEW.<replaceable class="parameter">column_name</replaceable></literal> |
| 229 |
or <literal>NEW.*</literal>. When an alias is provided, these names are |
| 230 |
hidden and the old or new rows must be referred to using the alias. |
| 231 |
For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>. |
| 232 |
</para> |
| 233 |
</listitem> |
| 234 |
</varlistentry> |
| 235 |
|
| 236 |
<varlistentry> |
| 237 |
<term><replaceable class="parameter">output_expression</replaceable></term> |
| 238 |
<listitem> |
| 239 |
<para> |
| 240 |
An expression to be computed and returned by the <command>UPDATE</command> |
| 241 |
command after each row is updated. The expression can use any |
| 242 |
column names of the table named by <replaceable class="parameter">table_name</replaceable> |
| 243 |
or table(s) listed in <literal>FROM</literal>. |
| 244 |
Write <literal>*</literal> to return all columns. |
| 245 |
</para> |
| 246 |
|
| 247 |
<para> |
| 248 |
A column name or <literal>*</literal> may be qualified using |
| 249 |
<literal>OLD</literal> or <literal>NEW</literal>, or the corresponding |
| 250 |
<replaceable class="parameter">output_alias</replaceable> for |
| 251 |
<literal>OLD</literal> or <literal>NEW</literal>, to cause old or new |
| 252 |
values to be returned. An unqualified column name, or |
| 253 |
<literal>*</literal>, or a column name or <literal>*</literal> qualified |
| 254 |
using the target table name or alias will return new values. |
| 255 |
</para> |
| 256 |
</listitem> |
| 257 |
</varlistentry> |
| 258 |
|
| 259 |
<varlistentry> |
| 260 |
<term><replaceable class="parameter">output_name</replaceable></term> |
| 261 |
<listitem> |
| 262 |
<para> |
| 263 |
A name to use for a returned column. |
| 264 |
</para> |
| 265 |
</listitem> |
| 266 |
</varlistentry> |
| 267 |
</variablelist> |
| 268 |
</refsect1> |
| 269 |
|
| 270 |
<refsect1> |
| 271 |
<title>Outputs</title> |
| 272 |
|
| 273 |
<para> |
| 274 |
On successful completion, an <command>UPDATE</command> command returns a command |
| 275 |
tag of the form |
| 276 |
<screen> |
| 277 |
UPDATE <replaceable class="parameter">count</replaceable> |
| 278 |
</screen> |
| 279 |
The <replaceable class="parameter">count</replaceable> is the number |
| 280 |
of rows updated, including matched rows whose values did not change. |
| 281 |
Note that the number may be less than the number of rows that matched |
| 282 |
the <replaceable class="parameter">condition</replaceable> when |
| 283 |
updates were suppressed by a <literal>BEFORE UPDATE</literal> trigger. If |
| 284 |
<replaceable class="parameter">count</replaceable> is 0, no rows were |
| 285 |
updated by the query (this is not considered an error). |
| 286 |
</para> |
| 287 |
|
| 288 |
<para> |
| 289 |
If the <command>UPDATE</command> command contains a <literal>RETURNING</literal> |
| 290 |
clause, the result will be similar to that of a <command>SELECT</command> |
| 291 |
statement containing the columns and values defined in the |
| 292 |
<literal>RETURNING</literal> list, computed over the row(s) updated by the |
| 293 |
command. |
| 294 |
</para> |
| 295 |
</refsect1> |
| 296 |
|
| 297 |
<refsect1> |
| 298 |
<title>Notes</title> |
| 299 |
|
| 300 |
<para> |
| 301 |
When a <literal>FROM</literal> clause is present, what essentially happens |
| 302 |
is that the target table is joined to the tables mentioned in the |
| 303 |
<replaceable>from_item</replaceable> list, and each output row of the join |
| 304 |
represents an update operation for the target table. When using |
| 305 |
<literal>FROM</literal> you should ensure that the join |
| 306 |
produces at most one output row for each row to be modified. In |
| 307 |
other words, a target row shouldn't join to more than one row from |
| 308 |
the other table(s). If it does, then only one of the join rows |
| 309 |
will be used to update the target row, but which one will be used |
| 310 |
is not readily predictable. |
| 311 |
</para> |
| 312 |
|
| 313 |
<para> |
| 314 |
Because of this indeterminacy, referencing other tables only within |
| 315 |
sub-selects is safer, though often harder to read and slower than |
| 316 |
using a join. |
| 317 |
</para> |
| 318 |
|
| 319 |
<para> |
| 320 |
In the case of a partitioned table, updating a row might cause it to no |
| 321 |
longer satisfy the partition constraint of the containing partition. In that |
| 322 |
case, if there is some other partition in the partition tree for which this |
| 323 |
row satisfies its partition constraint, then the row is moved to that |
| 324 |
partition. If there is no such partition, an error will occur. Behind the |
| 325 |
scenes, the row movement is actually a <command>DELETE</command> and |
| 326 |
<command>INSERT</command> operation. |
| 327 |
</para> |
| 328 |
|
| 329 |
<para> |
| 330 |
There is a possibility that a concurrent <command>UPDATE</command> or |
| 331 |
<command>DELETE</command> on the row being moved will get a serialization |
| 332 |
failure error. Suppose session 1 is performing an <command>UPDATE</command> |
| 333 |
on a partition key, and meanwhile a concurrent session 2 for which this |
| 334 |
row is visible performs an <command>UPDATE</command> or |
| 335 |
<command>DELETE</command> operation on this row. In such case, |
| 336 |
session 2's <command>UPDATE</command> or <command>DELETE</command> will |
| 337 |
detect the row movement and raise a serialization failure error (which |
| 338 |
always returns with an SQLSTATE code '40001'). Applications may wish to |
| 339 |
retry the transaction if this occurs. In the usual case where the table |
| 340 |
is not partitioned, or where there is no row movement, session 2 would |
| 341 |
have identified the newly updated row and carried out the |
| 342 |
<command>UPDATE</command>/<command>DELETE</command> on this new row |
| 343 |
version. |
| 344 |
</para> |
| 345 |
|
| 346 |
<para> |
| 347 |
Note that while rows can be moved from local partitions to a foreign-table |
| 348 |
partition (provided the foreign data wrapper supports tuple routing), they |
| 349 |
cannot be moved from a foreign-table partition to another partition. |
| 350 |
</para> |
| 351 |
|
| 352 |
<para> |
| 353 |
An attempt of moving a row from one partition to another will fail if a |
| 354 |
foreign key is found to directly reference an ancestor of the source |
| 355 |
partition that is not the same as the ancestor that's mentioned in the |
| 356 |
<command>UPDATE</command> query. |
| 357 |
</para> |
| 358 |
</refsect1> |
| 359 |
|
| 360 |
<refsect1> |
| 361 |
<title>Examples</title> |
| 362 |
|
| 363 |
<para> |
| 364 |
Change the word <literal>Drama</literal> to <literal>Dramatic</literal> in the |
| 365 |
column <structfield>kind</structfield> of the table <structname>films</structname>: |
| 366 |
|
| 367 |
<programlisting> |
| 368 |
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'; |
| 369 |
</programlisting> |
| 370 |
</para> |
| 371 |
|
| 372 |
<para> |
| 373 |
Adjust temperature entries and reset precipitation to its default |
| 374 |
value in one row of the table <structname>weather</structname>: |
| 375 |
|
| 376 |
<programlisting> |
| 377 |
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT |
| 378 |
WHERE city = 'San Francisco' AND date = '2003-07-03'; |
| 379 |
</programlisting> |
| 380 |
</para> |
| 381 |
|
| 382 |
<para> |
| 383 |
Perform the same operation and return the updated entries, and the old |
| 384 |
precipitation value: |
| 385 |
|
| 386 |
<programlisting> |
| 387 |
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT |
| 388 |
WHERE city = 'San Francisco' AND date = '2003-07-03' |
| 389 |
RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp; |
| 390 |
</programlisting> |
| 391 |
</para> |
| 392 |
|
| 393 |
<para> |
| 394 |
Use the alternative column-list syntax to do the same update: |
| 395 |
<programlisting> |
| 396 |
UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT) |
| 397 |
WHERE city = 'San Francisco' AND date = '2003-07-03'; |
| 398 |
</programlisting> |
| 399 |
</para> |
| 400 |
|
| 401 |
<para> |
| 402 |
Increment the sales count of the salesperson who manages the |
| 403 |
account for Acme Corporation, using the <literal>FROM</literal> |
| 404 |
clause syntax: |
| 405 |
<programlisting> |
| 406 |
UPDATE employees SET sales_count = sales_count + 1 FROM accounts |
| 407 |
WHERE accounts.name = 'Acme Corporation' |
| 408 |
AND employees.id = accounts.sales_person; |
| 409 |
</programlisting> |
| 410 |
</para> |
| 411 |
|
| 412 |
<para> |
| 413 |
Perform the same operation, using a sub-select in the |
| 414 |
<literal>WHERE</literal> clause: |
| 415 |
<programlisting> |
| 416 |
UPDATE employees SET sales_count = sales_count + 1 WHERE id = |
| 417 |
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); |
| 418 |
</programlisting> |
| 419 |
</para> |
| 420 |
|
| 421 |
<para> |
| 422 |
Update contact names in an accounts table to match the currently assigned |
| 423 |
salespeople: |
| 424 |
<programlisting> |
| 425 |
UPDATE accounts SET (contact_first_name, contact_last_name) = |
| 426 |
(SELECT first_name, last_name FROM employees |
| 427 |
WHERE employees.id = accounts.sales_person); |
| 428 |
</programlisting> |
| 429 |
A similar result could be accomplished with a join: |
| 430 |
<programlisting> |
| 431 |
UPDATE accounts SET contact_first_name = first_name, |
| 432 |
contact_last_name = last_name |
| 433 |
FROM employees WHERE employees.id = accounts.sales_person; |
| 434 |
</programlisting> |
| 435 |
However, the second query may give unexpected results |
| 436 |
if <structname>employees</structname>.<structfield>id</structfield> is not a unique key, whereas |
| 437 |
the first query is guaranteed to raise an error if there are multiple |
| 438 |
<structfield>id</structfield> matches. Also, if there is no match for a particular |
| 439 |
<structname>accounts</structname>.<structfield>sales_person</structfield> entry, the first query |
| 440 |
will set the corresponding name fields to NULL, whereas the second query |
| 441 |
will not update that row at all. |
| 442 |
</para> |
| 443 |
|
| 444 |
<para> |
| 445 |
Update statistics in a summary table to match the current data: |
| 446 |
<programlisting> |
| 447 |
UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) = |
| 448 |
(SELECT sum(x), sum(y), avg(x), avg(y) FROM data d |
| 449 |
WHERE d.group_id = s.group_id); |
| 450 |
</programlisting> |
| 451 |
</para> |
| 452 |
|
| 453 |
<para> |
| 454 |
Attempt to insert a new stock item along with the quantity of stock. If |
| 455 |
the item already exists, instead update the stock count of the existing |
| 456 |
item. To do this without failing the entire transaction, use savepoints: |
| 457 |
<programlisting> |
| 458 |
BEGIN; |
| 459 |
-- other operations |
| 460 |
SAVEPOINT sp1; |
| 461 |
INSERT INTO wines VALUES('Chateau Lafite 2003', '24'); |
| 462 |
-- Assume the above fails because of a unique key violation, |
| 463 |
-- so now we issue these commands: |
| 464 |
ROLLBACK TO sp1; |
| 465 |
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003'; |
| 466 |
-- continue with other operations, and eventually |
| 467 |
COMMIT; |
| 468 |
</programlisting> |
| 469 |
</para> |
| 470 |
|
| 471 |
<para> |
| 472 |
Change the <structfield>kind</structfield> column of the table |
| 473 |
<structname>films</structname> in the row on which the cursor |
| 474 |
<literal>c_films</literal> is currently positioned: |
| 475 |
<programlisting> |
| 476 |
UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; |
| 477 |
</programlisting> |
| 478 |
</para> |
| 479 |
|
| 480 |
<para id="update-limit"> |
| 481 |
Updates affecting many rows can have negative effects on system |
| 482 |
performance, such as table bloat, increased replica lag, and increased |
| 483 |
lock contention. In such situations it can make sense to perform the |
| 484 |
operation in smaller batches, possibly with a <command>VACUUM</command> |
| 485 |
operation on the table between batches. While there is |
| 486 |
no <literal>LIMIT</literal> clause for <command>UPDATE</command>, it is |
| 487 |
possible to get a similar effect through the use of |
| 488 |
a <link linkend="queries-with">Common Table Expression</link> and a |
| 489 |
self-join. With the standard <productname>PostgreSQL</productname> |
| 490 |
table access method, a self-join on the system |
| 491 |
column <link linkend="ddl-system-columns-ctid">ctid</link> is very |
| 492 |
efficient: |
| 493 |
<programlisting> |
| 494 |
WITH exceeded_max_retries AS ( |
| 495 |
SELECT w.ctid FROM work_item AS w |
| 496 |
WHERE w.status = 'active' AND w.num_retries > 10 |
| 497 |
ORDER BY w.retry_timestamp |
| 498 |
FOR UPDATE |
| 499 |
LIMIT 5000 |
| 500 |
) |
| 501 |
UPDATE work_item SET status = 'failed' |
| 502 |
FROM exceeded_max_retries AS emr |
| 503 |
WHERE work_item.ctid = emr.ctid; |
| 504 |
</programlisting> |
| 505 |
This command will need to be repeated until no rows remain to be updated. |
| 506 |
(This use of <structfield>ctid</structfield> is only safe because |
| 507 |
the query is repeatedly run, avoiding the problem of changed |
| 508 |
<structfield>ctid</structfield>s.) |
| 509 |
Use of an <literal>ORDER BY</literal> clause allows the command to |
| 510 |
prioritize which rows will be updated; it can also prevent deadlock |
| 511 |
with other update operations if they use the same ordering. |
| 512 |
If lock contention is a concern, then <literal>SKIP LOCKED</literal> |
| 513 |
can be added to the <acronym>CTE</acronym> to prevent multiple commands |
| 514 |
from updating the same row. However, then a |
| 515 |
final <command>UPDATE</command> without <literal>SKIP LOCKED</literal> |
| 516 |
or <literal>LIMIT</literal> will be needed to ensure that no matching |
| 517 |
rows were overlooked. |
| 518 |
</para> |
| 519 |
</refsect1> |
| 520 |
|
| 521 |
<refsect1> |
| 522 |
<title>Compatibility</title> |
| 523 |
|
| 524 |
<para> |
| 525 |
This command conforms to the <acronym>SQL</acronym> standard, except |
| 526 |
that the <literal>FROM</literal> and <literal>RETURNING</literal> clauses |
| 527 |
are <productname>PostgreSQL</productname> extensions, as is the ability |
| 528 |
to use <literal>WITH</literal> with <command>UPDATE</command>. |
| 529 |
</para> |
| 530 |
|
| 531 |
<para> |
| 532 |
Some other database systems offer a <literal>FROM</literal> option in which |
| 533 |
the target table is supposed to be listed again within <literal>FROM</literal>. |
| 534 |
That is not how <productname>PostgreSQL</productname> interprets |
| 535 |
<literal>FROM</literal>. Be careful when porting applications that use this |
| 536 |
extension. |
| 537 |
</para> |
| 538 |
|
| 539 |
<para> |
| 540 |
According to the standard, the source value for a parenthesized sub-list of |
| 541 |
target column names can be any row-valued expression yielding the correct |
| 542 |
number of columns. <productname>PostgreSQL</productname> only allows the |
| 543 |
source value to be a <link linkend="sql-syntax-row-constructors">row |
| 544 |
constructor</link> or a sub-<literal>SELECT</literal>. An individual column's |
| 545 |
updated value can be specified as <literal>DEFAULT</literal> in the |
| 546 |
row-constructor case, but not inside a sub-<literal>SELECT</literal>. |
| 547 |
</para> |
| 548 |
</refsect1> |
| 549 |
</refentry> |