Re: TRUNCATE TABLE with IDENTITY

Decibel! írta:
> On Mar 25, 2008, at 11:40 AM, Zoltan Boszormenyi wrote:
>> 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.
>
>
> I think you misunderstand what ALTER SEQUENCE RESTART does; it only 
> changes the current value of the sequence.

I didn't misunderstood, I know that. I quoted both
because (currently) CREATE SEQUENCE ... START WITH does the same.

zozo=> create sequence seq1 start with 327;
CREATE SEQUENCE
zozo=> select * from seq1;
 sequence_name | last_value | increment_by |      max_value      | 
min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 seq1          |        327 |            1 | 9223372036854775807 
|         1 |           1 |       1 | f         | f
(1 row)

Note the difference between "min_value" and "last_value".
Using the standard syntax of

CREATE TABLE (
   id integer IDENTITY GENERATED ALWAYS AS (START WITH 327),
   ...
);

and assuming you use the existing sequence infrastructure
there's a problem with TRUNCATE ... RESTART IDENTITY;
Where is the info in the sequence to provide restarting with
the _original_ start value?

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