Skip site navigation (1) Skip section navigation (2)

Re: TRUNCATE TABLE with IDENTITY

From: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
To: "Decibel!" <decibel(at)decibel(dot)org>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Steve Crawford <scrawford(at)pinpointresearch(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: TRUNCATE TABLE with IDENTITY
Date: 2008-04-03 05:52:25
Message-ID: 47F47099.7020806@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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/



In response to

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2008-04-03 07:12:58
Subject: Re: modules
Previous:From: Bruce MomjianDate: 2008-04-03 01:29:55
Subject: Re: Patch for pg_dump (function dumps)

pgsql-patches by date

Next:From: Pavel StehuleDate: 2008-04-03 06:09:31
Subject: Re: actualized SQL/PSM patch
Previous:From: Tom LaneDate: 2008-04-03 05:04:15
Subject: Re: actualized SQL/PSM patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group