Can we classify/say that TRUNCATE belongs to/falls under DML statement?
Check here for PostgreSQL TRUNCATE compatibility.
NOTE: TRUNCATE is part of SQL standard ANSI SQL 2008 - F200
+1
A:
I would say it's a DML statement in PostgreSQL: PostgreSQL has a TRUNCATE trigger but PostgreSQL doesn't have DDL triggers. So it can't be a DDL statement.
It acquires an ACCESS EXCLUSIVE lock on each table it operates on and it's not MVCC-safe but it's transactionsafe and you can do a rollback.
The ability to fire triggers for TRUNCATE is a PostgreSQL extension of the SQL standard.
Frank Heikens
2010-08-30 06:39:16
@Frank: May be this is off-topic from my original question, is it a DDL statement in Oracle? Any ideas/comments?
Gnanam
2010-09-01 06:13:03
You should ask an Oracle DBA, I don't have an opinion about it: lack of knowledge.
Frank Heikens
2010-09-01 06:42:05
orafaq says it's DDL: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands
Frank Heikens
2010-09-01 06:48:20
@Frank: Thanks for sharing that link. So based on what I've understood from here, can we say that classification of TRUNCATE statement as DDL or DML would be mainly decided based on the factor that whether the transaction is allowed to `ROLLBACK` or not?
Gnanam
2010-09-02 10:13:15
It depends on your database, there is not a single correct answer, DDL and DML are both correct: DDL in Oracle, DML in PostgreSQL. ROLLBACK can be used for almost everything in PostgreSQL, almost everything is transaction safe.
Frank Heikens
2010-09-02 11:28:13
A:
As TRUNCATE manipulates data and does not change any definition, I clearly see it as a DML statement.
a_horse_with_no_name
2010-08-30 06:51:44