User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0
MonetDB does not yet support the SQL command:
TRUNCATE TABLE <tablename>
The command is part of the SQL:2008 standard
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.
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;
F202 TRUNCATE TABLE
TRUNCATE [ TABLE ] qname [ CONTINUE IDENTITY | RESTART IDENTITY ] [ RESTRICT | CASCADE ]
functionality (and more) added in changeset
The Mar2018 version has been released.