summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-truncate.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
commit46651ce6fe013220ed397add242004d764fc0153 (patch)
tree6e5299f990f88e60174a1d3ae6e48eedd2688b2b /doc/src/sgml/html/sql-truncate.html
parentInitial commit. (diff)
downloadpostgresql-14-upstream.tar.xz
postgresql-14-upstream.zip
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--doc/src/sgml/html/sql-truncate.html119
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..865739b
--- /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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-unlisten.html" title="UNLISTEN">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-TRUNCATE"><div class="titlepage"></div><a id="id-1.9.3.180.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.180.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.180.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.180.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.5. Caveats">Section 13.5</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.35. postgres_fdw">postgres_fdw</a>.
+ </p></div><div class="refsect1" id="id-1.9.3.180.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.180.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.180.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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> UNLISTEN</td></tr></table></div></body></html> \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-15 22:27:10 +0000