Re: TRUNCATE TABLE with IDENTITY

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, ...);
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/