What happens to the Identity value when you Truncate a table in Exasol?

Today was challenging at work for many reasons, but it was a good day for learning new things!

I thought I’d share with you a nugget that I learnt today.

When I truncate a table, I expect the all of the data to be permanently deleted, essentially like it was never there. For all indexes to be emptied, and any auto-incrementing constraints to be reset.

Let’s give it a go then… *simples*…no?

CREATE TABLE ID_TESTING(
ID DECIMAL(18,0) IDENTITY NOT NULL ENABLE,
A DECIMAL(18,0),
B DECIMAL(18,0)
);

I then insert 3 rows:

insert into ID_TESTING (A,B)
SELECT 1,2;
insert into ID_TESTING (A,B)
SELECT 2,2;
insert into ID_TESTING (A,B)
SELECT 3,2;

This gives me 3 rows, with an identity value of 3, which we can check by looking in one of the sys tables.

SELECT column_identity
FROM SYS.EXA_ALL_COLUMNS
WHERE column_schema = 'DUMMY'
AND column_table = 'ID_TESTING'
AND column_name = 'ID';

Finally, I truncate the table;

truncate table ID_TESTING;

So if we check the identity value this should be zero right? Using the same query as above, we can see it remains at 3.

This is obviously not ideal, if you’re loading large amounts of data, or have not set your identity column to be DECIMAL(18,0), or you’re just plain expecting a counter to start at 1. Having got in touch with Exasol about this, as it just doesn’t make sense to me, it seems it had been raised as an issue previously, logged as IDEA 177, but has been discounted. As such, it makes this behaviour, accepted.

If, like me, this doesn’t really work for you; we need a workaround…

…I’ll cover it in my next blog post!