Man Pages

truncate(7) - phpMan truncate(7) - phpMan

Command: man perldoc info search(apropos)  


TRUNCATE(7)                      SQL Commands                      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 immedi-
       ately, 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. 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. 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).

              Warning:  TRUNCATE is not MVCC-safe (see in the documentation for general information about MVCC). After
              truncation, the table will appear empty to all concurrent transactions, even if they are using  a  snap-
              shot  taken  before  the  truncation occurred. This will only be an issue for a transaction that did not
              access the truncated table before the truncation happened -- any transaction that has done so would  hold
              at  least  an ACCESS SHARE lock, which would block TRUNCATE until that transaction completes. So trunca-
              tion will not cause any apparent inconsistency in the table contents for successive queries on the  same
              table,  but  it  could cause visible inconsistency between the contents of the truncated table and other
              tables in the database.


       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.

              Warning:  Any ALTER SEQUENCE RESTART operations performed as a consequence of using the RESTART IDENTITY
              option are nontransactional and will not be rolled back on failure. To minimize the risk,  these  opera-
              tions  are  performed only after all the rest of TRUNCATE's work is done. However, there is still a risk
              if TRUNCATE is performed inside a transaction block that is aborted afterwards. For example, consider

              BEGIN;
              TRUNCATE TABLE foo RESTART IDENTITY;
              COPY foo FROM ...;
              COMMIT;

              If the COPY fails partway through, the table data rolls back correctly, but the sequences will  be  left
              with  values  that are probably smaller than they had before, possibly leading to duplicate-key failures
              or other problems in later transactions.  If this is likely to be a problem, it's best  to  avoid  using
              RESTART IDENTITY, and accept that the new contents of the table will have higher serial numbers than the
              old.


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 CON-
       TINUE IDENTITY/RESTART IDENTITY also appear in that standard but have slightly different but 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.



SQL - Language Statements         2014-02-17                       TRUNCATE(7)