Changing minvalue of Postgres's sequence

I am trying to change minimum value of an existing sequence.

First I tried

ALTER SEQUENCE product_id_seq MINVALUE 10000;

and I got ERROR: START value (1) cannot be less than MINVALUE (10000).

So I tried

ALTER SEQUENCE product_id_seq MINVALUE 10000 RESTART WITH 10000;

but got the same error.

Of course I could just drop it and create a new开发者_Python百科 one, but I think there should be a way to do this. I am using Postgres 8.4.7.

How about setting them all at once:

ALTER SEQUENCE product_id_seq
MINVALUE 10000
START 10000
RESTART 10000;

That should change the minimum, starting, and current values all to 10000 and thus make everything consistent.

PostgreSQL has several functions that operate on sequences. In addition to the other guidance here, you could use

SELECT setval('product_id_seq ', 10000); -- Next nextval() returns 10001

I Have done the following test, My version is 9.0.

--create sequence
skytf=> CREATE SEQUENCE seq_test
skytf->     START WITH 1
skytf->     INCREMENT BY 1
skytf->     NO MINVALUE
skytf->     NO MAXVALUE
skytf->     CACHE 1;
CREATE SEQUENCE

skytf=> \d seq_test
           Sequence "skytf.seq_test"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | seq_test
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 1
 is_cycled     | boolean | f
 is_called     | boolean | f


skytf=> select nextval('seq_test');
 nextval 
---------
       1
(1 row)

--alter sequence 
skytf=> alter sequence seq_test  restart with 100;
ALTER SEQUENCE
skytf=> \d seq_test
           Sequence "skytf.seq_test"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | seq_test
 last_value    | bigint  | 100
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 1
 is_cycled     | boolean | f
 is_called     | boolean | f

skytf=> select nextval('seq_test');
 nextval 
---------
     100
(1 row)