Delete Contents Of A table

Question

How Can I delete Contents of A table.. If I have 100 rows in a table and i want to delete all of them without droping a table how would I?

Two answers, but they are different. TRUNCATE is a DDL statement. It won't perform integrity checks or run triggers on delete. — Benoit, Sep 28 '11 at 12:13
@Benoit and it will also issue a `commit` statement that will end the current transaction — Xavi López, Sep 28 '11 at 12:16
[What's the difference between TRUNCATE and DELETE in SQL](http://stackoverflow.com/q/139630/851811) — Xavi López, Sep 28 '11 at 12:22
@XaviLópez not in PostgreSQL (which the question is tagged). Truncates are transactional in PostgreSQL. http://www.postgresql.org/docs/current/static/sql-truncate.html "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." — rfusca, Sep 28 '11 at 15:18
@rfusca Thank you for providing this detail, unknown to me :) — Xavi López, Sep 28 '11 at 15:21
@Benoit: PostgreSQL will do integrity checks and can fire TRUNCATE triggers. It's also transaction safe, you can even do a rollback. — Frank Heikens, Sep 28 '11 at 17:29
Also, truncate in postgresql will reclaim all the disk space while delete will need vacuum to kick in to get it back. ALSO since truncate IS DDL, if you're running slony replication, the truncate will not get replicated. Pretty minor point unless you ARE in fact running slony, then it's a pretty major one. — Scott Marlowe, Sep 28 '11 at 19:37

Mark Byers · Answer 1 · 2011-09-28T12:18:23.160

4

Use DELETE without a WHERE clause:

DELETE FROM tablename

This may fail if you have other tables that refer to it via foreign key references.

edited Sep 28 '11 at 12:18

answered Sep 28 '11 at 12:04

Mark Byers

@Scorpion: in **PostgreSQL**, in the case of linked rows in other tables (via foreign key constraint) you can can cascade the DELETE like this `DELETE FROM tablename CASCADE`. But be very careful with that! You may or may not want to delete related rows. – Erwin Brandstetter Sep 28 '11 at 17:29
Note that you can always delete in a transaction to see if you're losing rows you don't want to. In pgsql everything except create/drop database/tablespace are transactable. – Scott Marlowe Sep 28 '11 at 19:39

score 4 · Accepted Answer · answered Sep 28 '11 at 12:12

4

Both given answers are correct.

The main difference between

TRUNCATE TABLE tablename

and

DELETE FROM tablename

is that TRUNCATE will reset the auto_increment value back to 1. It may make a difference if things have to be unique.

answered Sep 28 '11 at 12:12

Eljakim

2

Question is tagged PostgreSQL as well. Postgres doesn't have that insane auto_increment issue. http://www.postgresql.org/docs/current/static/sql-truncate.html – rfusca Sep 28 '11 at 15:20
And PostgreSQL doesn't reset any sequence, unless you ask for it using RESTART IDENTITY. – Frank Heikens Sep 28 '11 at 17:31

reader_1000 · Answer 3 · 2011-09-28T16:27:46.437

3

You can also use TRUNCATE

TRUNCATE TABLE tablename

edited Sep 28 '11 at 16:27

answered Sep 28 '11 at 12:06

reader_1000

score -1 · Answer 4 · answered Sep 28 '11 at 12:14

-1

You can use Truncate also,it's faster than DELETE but TRUNCATE will not work under a table lock or transaction.

have a look at

answered Sep 28 '11 at 12:14

Peeyush

2

Question is tagged PostgreSQL as well. Truncates are transactional in postgres. http://www.postgresql.org/docs/current/static/sql-truncate.html – rfusca Sep 28 '11 at 15:20

4 Answers4