Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
TRUNCATE(7)		PostgreSQL 9.6.3 Documentation		   TRUNCATE(7)

NAME
       TRUNCATE	- empty	a table	or set of tables

SYNOPSIS
       TRUNCATE	[ TABLE	] [ ONLY ] name	[ * ] [, ... ]
	   [ RESTART IDENTITY |	CONTINUE IDENTITY ] [ CASCADE |	RESTRICT ]

DESCRIPTION
       TRUNCATE	quickly	removes	all rows from a	set of tables. It has the same
       effect as an unqualified	DELETE 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 VACUUM operation.
       This is most useful on large tables.

PARAMETERS
       name
	   The name (optionally	schema-qualified) of a table to	truncate. If
	   ONLY	is specified before the	table name, only that table is
	   truncated. If ONLY is not specified,	the table and all its
	   descendant tables (if any) are truncated. Optionally, * can be
	   specified after the table name to explicitly	indicate that
	   descendant tables are included.

       RESTART IDENTITY
	   Automatically restart sequences owned by columns of the truncated
	   table(s).

       CONTINUE	IDENTITY
	   Do not change the values of sequences. This is the default.

       CASCADE
	   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 CASCADE.

       RESTRICT
	   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.

NOTES
       You must	have the TRUNCATE privilege on a table to truncate it.

       TRUNCATE	acquires an ACCESS EXCLUSIVE lock on each table	it operates
       on, which blocks	all other concurrent operations	on the table. When
       RESTART IDENTITY	is specified, any sequences that are to	be restarted
       are likewise locked exclusively.	If concurrent access to	a table	is
       required, then the DELETE command should	be used	instead.

       TRUNCATE	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 CASCADE 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!

       TRUNCATE	will not fire any ON DELETE triggers that might	exist for the
       tables. But it will fire	ON TRUNCATE triggers. If ON TRUNCATE triggers
       are defined for any of the tables, then all BEFORE TRUNCATE triggers
       are fired before	any truncation happens,	and all	AFTER TRUNCATE
       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).

       TRUNCATE	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 Section 13.5, "Caveats", in the
       documentation for more details.

       TRUNCATE	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.

       When RESTART IDENTITY is	specified, the implied ALTER SEQUENCE RESTART
       operations are also done	transactionally; that is, they will be rolled
       back if the surrounding transaction does	not commit. This is unlike the
       normal behavior of ALTER	SEQUENCE RESTART. 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 currval();
       that is,	after the transaction currval()	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	currval() after	a failed transaction.

       TRUNCATE	is not currently supported for foreign tables. This implies
       that if a specified table has any descendant tables that	are foreign,
       the command will	fail.

EXAMPLES
       Truncate	the tables bigtable and	fattable:

	   TRUNCATE bigtable, fattable;

       The same, and also reset	any associated sequence	generators:

	   TRUNCATE bigtable, fattable RESTART IDENTITY;

       Truncate	the table othertable, and cascade to any tables	that reference
       othertable via foreign-key constraints:

	   TRUNCATE othertable CASCADE;

COMPATIBILITY
       The SQL:2008 standard includes a	TRUNCATE command with the syntax
       TRUNCATE	TABLE tablename. The clauses CONTINUE IDENTITY/RESTART
       IDENTITY	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.

PostgreSQL 9.6.3		     2017			   TRUNCATE(7)

NAME | SYNOPSIS | DESCRIPTION | PARAMETERS | NOTES | EXAMPLES | COMPATIBILITY

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=TRUNCATE&sektion=7&manpath=FreeBSD+12.0-RELEASE+and+Ports>

home | help