Bug 6244 - Add support for: TRUNCATE TABLE
Summary: Add support for: TRUNCATE TABLE
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: Other All
: Normal enhancement
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-03-16 18:00 CET by Martin van Dinther
Modified: 2018-03-29 15:39 CEST (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther 2017-03-16 18:00:23 CET
User-Agent:       Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0
Build Identifier: 

MonetDB does not yet support the SQL command:
 TRUNCATE TABLE <tablename>
See: https://en.wikipedia.org/wiki/Truncate_(SQL)
The command is part of the SQL:2008 standard
See: https://en.wikipedia.org/wiki/SQL:2008

In DWH situations were data needs to be refreshed often (via ETL or import of external csv data) it would be nice to be able to quickly remove all data from an existing table, before loading the new data.
Support for TRUNCATE TABLE would be good for performance, compatibility and ease of migration reasons.

The implementation should (of course) be faster than the alternative:
 DELETE FROM <tablename>;
This could for instance be done by excluding the deleted rows from the transaction log (and thus a rollback of this command is no longer possible).
Also we could move the existing BATs (of the columns of the table) and create new empty ones. On commit remove the moved BATs, on rollback undo the move.

Reproducible: Always

Steps to Reproduce:
1. CREATE TABLE import_mydata (ID int NOT NULL PRIMARY KEY, name varchar(30) NOT NULL, x double, y double, z double);
2. COPY INTO import_mydata FROM 'mydata_2017_03_16.tsv';
3. SELECT COUNT(*) AS count FROM import_mydata;
4. TRUNCATE TABLE import_mydata;
5. SELECT COUNT(*) AS count FROM import_mydata;




https://www.monetdb.org/Documentation/Manuals/SQLreference/Features/unsupported
  F202 	TRUNCATE TABLE

https://en.wikipedia.org/wiki/Truncate_(SQL)
https://en.wikipedia.org/wiki/SQL:2008
Comment 1 Martin van Dinther 2018-02-08 18:45:57 CET
TRUNCATE [ TABLE ] qname [ CONTINUE IDENTITY | RESTART IDENTITY ] [ RESTRICT | CASCADE ]
functionality (and more) added in changeset
 https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2838cc1c63b5
Comment 2 Sjoerd Mullender cwiconfidential 2018-03-29 15:39:12 CEST
The Mar2018 version has been released.