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(at)tm(dot)ee>
#
# ---------------------------(end of broadcast)---------------------------
# TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
#
#

--
SPY My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin(at)spy(dot)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. ____________