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!

 

5 thoughts on “What happens to the Identity value when you Truncate a table in Exasol?

  1. EXADude says:

    After TRUNCATE, if you want the identity to reset, do this :-

    ALTER TABLE id_testing ALTER COLUMN id SET IDENTITY(0);

    (or have I just spoiled your next blog post)

    I know you’d like EXASOL to work just like SQLServer, but this is also the default behaviour found in MySQL, PostgreSQL and a ton of other databases. SQLServer is very much the exception.

    • Rose Ahearne says:

      Thanks, that is the next post! Always good for some audience participation/heckling though 😉

      One of the many purposes of this blog is my personal technical log, but also to serve as a shortcut for those that stumble into the same pitfalls that I have. Coming from a SQL Server background, we sometimes take that functionality as a given.

      • EXAdude says:

        You’re welcome – I came to EXASOL from a Netezza background and I’m still making adjustments.

        Also I ran a blog for a while and I know how good it is to get some kind of feedback – even from the Anonymous crazy people.

Leave a Reply