diff options
Diffstat (limited to 'doc/src/sgml/html/sql-truncate.html')
| -rw-r--r-- | doc/src/sgml/html/sql-truncate.html | 119 |
1 files changed, 119 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-truncate.html b/doc/src/sgml/html/sql-truncate.html new file mode 100644 index 0000000..55846ff --- /dev/null +++ b/doc/src/sgml/html/sql-truncate.html @@ -0,0 +1,119 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>TRUNCATE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-start-transaction.html" title="START TRANSACTION" /><link rel="next" href="sql-unlisten.html" title="UNLISTEN" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">TRUNCATE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-start-transaction.html" title="START TRANSACTION">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-unlisten.html" title="UNLISTEN">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-TRUNCATE"><div class="titlepage"></div><a id="id-1.9.3.181.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">TRUNCATE</span></h2><p>TRUNCATE — empty a table or set of tables</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +TRUNCATE [ TABLE ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ] [, ... ] + [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] +</pre></div><div class="refsect1" id="id-1.9.3.181.5"><h2>Description</h2><p> + <code class="command">TRUNCATE</code> quickly removes all rows from a set of + tables. It has the same effect as an unqualified + <code class="command">DELETE</code> on each table, but since it does not actually + scan the tables it is faster. Furthermore, it reclaims disk space + immediately, rather than requiring a subsequent <code class="command">VACUUM</code> + operation. This is most useful on large tables. + </p></div><div class="refsect1" id="id-1.9.3.181.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of a table to truncate. + If <code class="literal">ONLY</code> is specified before the table name, only that table + is truncated. If <code class="literal">ONLY</code> is not specified, the table and all + its descendant tables (if any) are truncated. Optionally, <code class="literal">*</code> + can be specified after the table name to explicitly indicate that + descendant tables are included. + </p></dd><dt><span class="term"><code class="literal">RESTART IDENTITY</code></span></dt><dd><p> + Automatically restart sequences owned by columns of + the truncated table(s). + </p></dd><dt><span class="term"><code class="literal">CONTINUE IDENTITY</code></span></dt><dd><p> + Do not change the values of sequences. This is the default. + </p></dd><dt><span class="term"><code class="literal">CASCADE</code></span></dt><dd><p> + Automatically truncate all tables that have foreign-key references + to any of the named tables, or to any tables added to the group + due to <code class="literal">CASCADE</code>. + </p></dd><dt><span class="term"><code class="literal">RESTRICT</code></span></dt><dd><p> + Refuse to truncate if any of the tables have foreign-key references + from tables that are not listed in the command. This is the default. + </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.181.7"><h2>Notes</h2><p> + You must have the <code class="literal">TRUNCATE</code> privilege on a table + to truncate it. + </p><p> + <code class="command">TRUNCATE</code> acquires an <code class="literal">ACCESS EXCLUSIVE</code> lock on each + table it operates on, which blocks all other concurrent operations + on the table. When <code class="literal">RESTART IDENTITY</code> is specified, any + sequences that are to be restarted are likewise locked exclusively. + If concurrent access to a table is required, then + the <code class="command">DELETE</code> command should be used instead. + </p><p> + <code class="command">TRUNCATE</code> cannot be used on a table that has foreign-key + references from other tables, unless all such tables are also truncated + in the same command. Checking validity in such cases would require table + scans, and the whole point is not to do one. The <code class="literal">CASCADE</code> + option can be used to automatically include all dependent tables — + but be very careful when using this option, or else you might lose data you + did not intend to! + Note in particular that when the table to be truncated is a partition, + siblings partitions are left untouched, but cascading occurs to all + referencing tables and all their partitions with no distinction. + </p><p> + <code class="command">TRUNCATE</code> will not fire any <code class="literal">ON DELETE</code> + triggers that might exist for the tables. But it will fire + <code class="literal">ON TRUNCATE</code> triggers. + If <code class="literal">ON TRUNCATE</code> triggers are defined for any of + the tables, then all <code class="literal">BEFORE TRUNCATE</code> triggers are + fired before any truncation happens, and all <code class="literal">AFTER + TRUNCATE</code> triggers are fired after the last truncation is + performed and any sequences are reset. + The triggers will fire in the order that the tables are + to be processed (first those listed in the command, and then any + that were added due to cascading). + </p><p> + <code class="command">TRUNCATE</code> is not MVCC-safe. After truncation, the table will + appear empty to concurrent transactions, if they are using a snapshot + taken before the truncation occurred. + See <a class="xref" href="mvcc-caveats.html" title="13.6. Caveats">Section 13.6</a> for more details. + </p><p> + <code class="command">TRUNCATE</code> is transaction-safe with respect to the data + in the tables: the truncation will be safely rolled back if the surrounding + transaction does not commit. + </p><p> + When <code class="literal">RESTART IDENTITY</code> is specified, the implied + <code class="command">ALTER SEQUENCE RESTART</code> operations are also done + transactionally; that is, they will be rolled back if the surrounding + transaction does not commit. Be aware that if any additional + sequence operations are done on the restarted sequences before the + transaction rolls back, the effects of these operations on the sequences + will be rolled back, but not their effects on <code class="function">currval()</code>; + that is, after the transaction <code class="function">currval()</code> will continue to + reflect the last sequence value obtained inside the failed transaction, + even though the sequence itself may no longer be consistent with that. + This is similar to the usual behavior of <code class="function">currval()</code> after + a failed transaction. + </p><p> + <code class="command">TRUNCATE</code> can be used for foreign tables if + supported by the foreign data wrapper, for instance, + see <a class="xref" href="postgres-fdw.html" title="F.38. postgres_fdw">postgres_fdw</a>. + </p></div><div class="refsect1" id="id-1.9.3.181.8"><h2>Examples</h2><p> + Truncate the tables <code class="literal">bigtable</code> and + <code class="literal">fattable</code>: + +</p><pre class="programlisting"> +TRUNCATE bigtable, fattable; +</pre><p> + </p><p> + The same, and also reset any associated sequence generators: + +</p><pre class="programlisting"> +TRUNCATE bigtable, fattable RESTART IDENTITY; +</pre><p> + </p><p> + Truncate the table <code class="literal">othertable</code>, and cascade to any tables + that reference <code class="literal">othertable</code> via foreign-key + constraints: + +</p><pre class="programlisting"> +TRUNCATE othertable CASCADE; +</pre></div><div class="refsect1" id="id-1.9.3.181.9"><h2>Compatibility</h2><p> + The SQL:2008 standard includes a <code class="command">TRUNCATE</code> command + with the syntax <code class="literal">TRUNCATE TABLE + <em class="replaceable"><code>tablename</code></em></code>. The clauses + <code class="literal">CONTINUE IDENTITY</code>/<code class="literal">RESTART IDENTITY</code> + also appear in that standard, but have slightly different though related + meanings. Some of the concurrency behavior of this command is left + implementation-defined by the standard, so the above notes should be + considered and compared with other implementations if necessary. + </p></div><div class="refsect1" id="id-1.9.3.181.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-delete.html" title="DELETE"><span class="refentrytitle">DELETE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-start-transaction.html" title="START TRANSACTION">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-unlisten.html" title="UNLISTEN">Next</a></td></tr><tr><td width="40%" align="left" valign="top">START TRANSACTION </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> UNLISTEN</td></tr></table></div></body></html>
\ No newline at end of file |