Home > APIs > YSQL > The SQL language > SQL statements >

TRUNCATE

Synopsis

Use the TRUNCATE statement to remove all rows from the specified table or, optionally, to remove all rows from all the tables in the closure with foreign key references to the specified table.

Applying TRUNCATE to a set of tables produces the same ultimate outcome as does using an unrestricted DELETE on each table in the set; but it doesn't scan the tables. TRUNCATE is therefore faster than DELETE. It also reclaims disk space immediately. This means that the larger is the table, the more greater the performance benefit of TRUNCATE is.

truncate ::= TRUNCATE [ TABLE ] { table_expr [ , ... ] } 
             [ CASCADE | RESTRICT ]

truncate

Table inheritance is not yet supported

The table_expr rule specifies syntax that is useful only when at least one other table inherits one of the tables that the truncate statement lists explicitly. See this note for more detail. Until inheritance is supported, use a bare table_name.

Semantics

Specify the name of the table to be truncated.

TRUNCATE acquires ACCESS EXCLUSIVE lock on the tables to be truncated. The ACCESS EXCLUSIVE locking option is not yet fully supported.
TRUNCATE is not supported for foreign tables.
CASCADE and RESTRICT affect what happens when the table that the TRUNCATE statement targets has dependent tables. A dependent table (and, in turn, its dependent tables) have that status because they have direct or transitive foreign key constrains to the target table. CASCADE causes the closure of dependent tables all to be truncated. And RESTRICT causes the TRUNCATE attempt to fail if the target table has any dependent tables. This error outcome is the same even when all of the tables are empty. If neither CASCADE nor RESTRICT is written, then the effect is as if RESTRICT had been written.

Example

First create a parent-child table pair and populate them:

drop table if exists children cascade;
drop table if exists parents  cascade;

create table parents(k int primary key, v text not null);

create table children(
  parent_k  int  not null,
  k         int  not null,
  v         text not null,

  constraint children_pk primary key(parent_k, k),

  constraint children_fk foreign key(parent_k)
    references parents(k)
    match full
    on delete cascade
    on update restrict);

insert into parents(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog');

insert into children(parent_k, k, v) values
  (1, 1, 'dog-child-a'),
  (1, 2, 'dog-child-b'),
  (1, 3, 'dog-child-c'),
  (2, 1, 'cat-child-a'),
  (2, 2, 'cat-child-b'),
  (2, 3, 'cat-child-c'),
  (3, 1, 'frog-child-a'),
  (3, 2, 'frog-child-b'),
  (3, 3, 'frog-child-c');

select p.v as "parents.v", c.v as "children.v"
from parents p inner join children c on c.parent_k = p.k
order by p.k, c.k;

This is the result:

 parents.v |  children.v  
-----------+--------------
 dog       | dog-child-a
 dog       | dog-child-b
 dog       | dog-child-c
 cat       | cat-child-a
 cat       | cat-child-b
 cat       | cat-child-c
 frog      | frog-child-a
 frog      | frog-child-b
 frog      | frog-child-c

The \d children metacommand shows that it has a foreign key constraint to the parents table. This makes it a (transitive) dependent object of that table:

Indexes:
    "children_pk" PRIMARY KEY, lsm (parent_k HASH, k ASC)
Foreign-key constraints:
    "children_fk" FOREIGN KEY (parent_k) REFERENCES parents(k) MATCH FULL ON UPDATE RESTRICT ON DELETE CASCADE

Notice that the effect of the on delete cascade clause is limited to what the delete statement does. It has no effect on the behavior of truncate. (There is no on truncate cascade clause.) Try delete from parents. It quietly succeeds and removes all the rows from both the parents table and the children table.

With all the rows that the setup code above inserts, try this:

do $body$
declare
  message  text not null := '';
  detail   text not null := '';
begin
  -- Causes error 'cos "cascade" is required.
  truncate table parents;
  assert false, 'Should not get here';
exception
  -- Error 0A000
  when feature_not_supported then
    get stacked diagnostics
      message  = message_text,
      detail   = pg_exception_detail;
    assert message = 'cannot truncate a table referenced in a foreign key constraint',  'Bad message';
    assert detail  = 'Table "children" references "parents".',                          'Bad detail';
end;
$body$;

It finishes without error, showing that the bare truncate table parents, without cascade, fails and causes the message and hint that the code presents. Now repeat the attempt with cascade and observe the result:

truncate table parents cascade;

select
  (select count(*) from parents) as "parents count",
  (select count(*) from children) as "children count";

The truncate statement now finishes without error. This is the result:

 parents count | children count 
---------------+----------------
             0 |              0

Finally, try truncate table parents again. As promised, it still fails with the 0A000 error, even though the transitively dependent table, children, is empty.