Re: [HACKERS] how to alter sequence.

Around 20:41 on Dec 4, 2002, Hannu Krosing said:

    What's wrong with this:

dustin=# create sequence test_seq;
CREATE SEQUENCE
dustin=# select nextval('test_seq');
 nextval
---------
       1
(1 row)

dustin=# select setval('test_seq', 9999);
 setval
--------
   9999
(1 row)

dustin=# select nextval('test_seq');
 nextval
---------
   10000
(1 row)


# Oliver Elphick kirjutas K, 04.12.2002 kell 19:06:
# > On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote:
# > > Hai friends,
# > > I have a sequence called raj_seq with max value 3000.
# > ...
# > > now i wanted to increase the max value of the raj_seq
# > > to 9999999.
# > > How to do this change?
# > > If i drop and recreate the raj_seq, then i have to
# > > recreate the table and all triggers working on that
# > > table.But it is not an acceptable solution.
# > > So with out droping raj_seq , how do I solve this
# > > problem.
# >
# > Unfortunately there doesn't seem to be any easy way to do this.  There
# > is no ALTER SEQUENCE command and you can't use UPDATE on a sequence.
# >
# > Hackers: Could this be a TODO item for 7.4?
#
# This seems to work - as an example why we need the TODO ;)
#
# hannu=# update seq set max_value = 99;
# ERROR:  You can't change sequence relation seq
# hannu=# update pg_class set relkind = 'r' where relname = 'seq';
# UPDATE 1
# hannu=# update seq set max_value = 99;
# UPDATE 1
# hannu=# update pg_class set relkind = 'S' where relname = 'seq';
# UPDATE 1
# hannu=# select * from seq;
#  sequence_name | last_value | increment_by | max_value | min_value |
# cache_value | log_cnt | is_cycled | is_called
# ---------------+------------+--------------+-----------+-----------+-------------+---------+-----------+-----------
#  seq           |          1 |            1 |        99 |         1
# |           1 |       1 | f         | f
# (1 row)
#
# I can't really recommend it, because it may (or may not ;) have some
# unwanted behaviours as well;
#
#
# >
# > The easiest way to do this at present is probably to dump the database,
# > edit the dump to change the sequence max_value and then recreate the
# > database from the edited dump.  I presume you used CREATE SEQUENCE in
# > order to get such a low max_value.  If it were created from a SERIAL
# > datatype, you would also have to edit the table definition to use a
# > pre-created sequence.  There is no means of specifying a max_value using
# > SERIAL.
# --
# Hannu Krosing <hannu@tm.ee>
#
# ---------------------------(end of broadcast)---------------------------
# TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
#
#

--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________