Re: TRUNCATE TABLE with IDENTITY

Zoltan Boszormenyi írta:
> Simon Riggs írta:
>> On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote:
>>
>>> Simon Riggs wrote:
>>>
>>>> RESTART IDENTITY will reset the SERIAL sequences back to the original
>>>> start value.
>>>>
>>> Assuming this feature were to be added....
>>>
>>> In cases where the same sequence has been used across multiple
>>> tables, what will be the appropriate response when a user attempts
>>> to TRUNCATE one of those tables with RESTART IDENTITY?
>>>
>>
>> Well, I'm suggesting it as a TODO item, based on the standard. It would
>> be for whoever took this up to unravel that.
>>
>> Since that's a weak answer, I'd say it should only reset sequences that
>> have been placed there automatically through the use of SERIAL or
>> BIGSERIAL datatypes.
>>
>
> All of them? PostgreSQL allow multiple SERIALs to be present,
> the standard allows only one IDENTITY column in a table.
> And what about this case below?
>
> CREATE TABLE t1 (id1 serial, ...);
> ALTER SEQUENCE seq_t1_id1 RESTART WITH 5432 CYCLE;
>
> or the equivalent
>
> CREATE SEQUENCE seq_t1_id1 START WITH 5432 CYCLE;
> CREATE TABLE t1 (id1 serial, ...);

of course
CREATE TABLE t1 (id1 integer, ...);

> ALTER SEQUENCE seq_t1_id1 OWNED BY t1.id1;
>
> PostgreSQL doesn't keep the START WITH information.
> But it should to perform a "restart" on the sequence,
> using the minval in this case wouldn't be correct.
>

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