Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for: TRUNCATE TABLE #6244

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Add support for: TRUNCATE TABLE #6244

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2017-03-16 18:00:23 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: -- development

Last updated: 2018-03-29 15:39:12 +0200

Comment 25154

Date: 2017-03-16 18:00:23 +0100
From: Martin van Dinther <<martin.van.dinther>>

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
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 ;
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 26181

Date: 2018-02-08 18:45:57 +0100
From: Martin van Dinther <<martin.van.dinther>>

TRUNCATE [ TABLE ] qname [ CONTINUE IDENTITY | RESTART IDENTITY ] [ RESTRICT | CASCADE ]
functionality (and more) added in changeset
https//devmonetdborg/hg/MonetDB?cmd=changeset;node=2838cc1c63b5

Comment 26308

Date: 2018-03-29 15:39:12 +0200
From: @sjoerdmullender

The Mar2018 version has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant