Hello Ingo, Kostja and the others,
by accident last Ingo reply here got my attention.
Anyway, I made some SQL Standard researches about the topics truncate
and auto_inc.
First of all:
Neither TRUNCATE nor AUTO_INCREMENT are in the SQL Standard.
But SERIAL is.
We implemented SERIAL more or minder as an aliases of a BIGINT AUTO_INC
NOT NULL UNIQUE column.
Generally, our AUTO_INCREMENT feature is really close to a "fix"
ascending SEQUENCE. SEQUENCES also are defined in SQL Standard.
The only possibilities according to SQL Standard to reset values in
SEQUENCE are:
1) DROP/RECREATE SEQUENCE
2) ALTER SEQUENCE (ALTER start value, increment value and so on)
SEQUENCE and TABLE are two different objects in SQL Standard.
According to this my logic says that TRUNCATE should clean the TABLE but
let the sequence (AUTO_INC) as what it is.
Please, never forget that SERIAL is defined in SQL Standard and it is
defined as INTEGER column with DEFAULT SEQUENCE with start value of 1
and increment value of 1.
TRUNCATE itself is not defined in SQL Standard but besides us PostgreSQL
has TRUNCATE. Also afair Informix and DB2 having TRUNCATE same way as
PostgreSQL.
I looked into PG docs here:
http://www.postgresql.org/docs/8.3/interactive/sql-truncate.html
Of course I also tested it. As I expected PG Truncate is not resetting
SERIAL (I mean the SEQUENCE here).
When you really want to reset the SEQUENCE in PostgreSQL you really need
to ALTER SEQUENCE or use SETVAL() function. Usually, PostgreSQL will
throw an error when you try to DROP a SEQUENCE that is used in a column
besides you are using CASCADE. And when you use DROP CASCADE your whole
column will be deleted too.
According to my researches here I would recommend that we avoid to reset
AUTO_INC during TRUNCATE. Users won't expect such a behaviour.
Especially not, when they used SERIAL. The only way to change/reset
AUTO_INC should be via ALTER.
Just my 2 cent.
Susanne
--
Sun Microsystems GmbH
Dipl.-Inf. Susanne Ebrecht, Support Engineer
33615 Bielefeld, Germany
http://www.sun.com
Registered Office: Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Commercial register of the Local Court of Munich: HRB 161028
Managing Directors: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Chairman of the Supervisory Board: Martin Haering
