Re: TRUNCATE TABLE with IDENTITY

Decibel! írta:
> On Apr 3, 2008, at 12:52 AM, Zoltan Boszormenyi wrote:
>> Where is the info in the sequence to provide restarting with
>> the _original_ start value?
>
> There isn't any. If you want the sequence to start at some magic
> value, adjust the minimum value.

There's the START WITH option for IDENTITY columns and this below
is paragraph 8 under General rules of 14.10 <truncate table statement>
in 6WD2_02_Foundation_2007-12.pdf (page 902):

8) If RESTART IDENTITY is specified and the table descriptor of T
includes a column descriptor IDCD of
an identity column, then:
a) Let CN be the column name included in IDCD and let SV be the start
value included in IDCD.
b) The following <alter table statement> is effectively executed
without further Access Rule checking:
ALTER TABLE TN ALTER COLUMN CN RESTART WITH SV

This says that the original start value is used, not the minimum value.
IDENTITY has the same options as CREATE SEQUENCE. In fact the
"identity column specification" links to "11.63 <sequence generator
definition>"
when it comes to IDENTITY sequence options. And surprise, surprise,
"11.64 <alter sequence generator statement>" now defines
ALTER SEQUENCE sn RESTART [WITH newvalue]
where omitting the "WITH newval" part also uses the original start value.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/