Re: [HACKERS] TRUNCATE TABLE with IDENTITY

LinkBack

Thread Tools

Display Modes

#1 (permalink)

04-19-2008, 06:28 AM

Zoltan Boszormenyi

Posts: n/a

Re: [HACKERS] TRUNCATE TABLE with IDENTITY

Zoltan Boszormenyi írta:
> 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

Attached patch implements the extension found in the current SQL200n draft,
implementing stored start value and supporting ALTER SEQUENCE seq RESTART;
Some error check are also added to prohibit CREATE SEQUENCE ... RESTART ...
and ALTER SEQUENCE ... START ...

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

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

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

« Headers dependencies cleanup | Indexam API changes »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

All times are GMT. The time now is 03:48 PM.

SEO

Re: [HACKERS] TRUNCATE TABLE with IDENTITY