Обсуждение: setting serial start value

I have a table with a field called "id" that is a serial field and
pkey. I would like to set the the sequence to start at 10000 and
increase sequentially from there but I can't seem to get that to
work. any suggestions are greatly appreciated.

I am using version 7.4.7
Greg

On Thu, Jul 20, 2006 at 09:32:56PM -0400, Greg Philpott wrote:
> I have a table with a field called "id" that is a serial field and
> pkey. I would like to set the the sequence to start at 10000 and
> increase sequentially from there but I can't seem to get that to
> work. any suggestions are greatly appreciated.

What have you tried?  ALTER SEQUENCE or setval() should work.

http://www.postgresql.org/docs/7.4/interactive/sql-altersequence.html
http://www.postgresql.org/docs/7.4/interactive/functions-sequence.html

--
Michael Fuhr

Hi Michael, from terminal in psql I enter
# ALTER SEQUENCE public.users MINVALUE 9999;
But it doesn't  work. I don't think I am specifying the field
correctly. the schema is public, the table is users, the field is id.
Thanks,
Greg
On 20-Jul-06, at 10:49 PM, Michael Fuhr wrote:

> On Thu, Jul 20, 2006 at 09:32:56PM -0400, Greg Philpott wrote:
>> I have a table with a field called "id" that is a serial field and
>> pkey. I would like to set the the sequence to start at 10000 and
>> increase sequentially from there but I can't seem to get that to
>> work. any suggestions are greatly appreciated.
>
> What have you tried?  ALTER SEQUENCE or setval() should work.
>
> http://www.postgresql.org/docs/7.4/interactive/sql-altersequence.html
> http://www.postgresql.org/docs/7.4/interactive/functions-sequence.html
>
> --
> Michael Fuhr

On Thu, Jul 20, 2006 at 11:35:51PM -0400, Greg Philpott wrote:
> Hi Michael, from terminal in psql I enter
> # ALTER SEQUENCE public.users MINVALUE 9999;
> But it doesn't  work. I don't think I am specifying the field
> correctly. the schema is public, the table is users, the field is id.

ALTER SEQUENCE uses the sequence name, not the table name.  Also,
use RESTART WITH to set the sequence's current value.  Example:

test=> CREATE TABLE users (id serial PRIMARY KEY, username text);
NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for "serial" column "users.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE
test=> ALTER SEQUENCE users_id_seq RESTART WITH 10000;
ALTER SEQUENCE
test=> INSERT INTO users (username) VALUES ('Alice');
INSERT 2592322 1
test=> INSERT INTO users (username) VALUES ('Bob');
INSERT 2592323 1
test=> SELECT * FROM users;
  id   | username
-------+----------
 10000 | Alice
 10001 | Bob
(2 rows)

--
Michael Fuhr

Thanks Michael that did the trick!
Greg
On 21-Jul-06, at 12:05 AM, Michael Fuhr wrote:

> On Thu, Jul 20, 2006 at 11:35:51PM -0400, Greg Philpott wrote:
>> Hi Michael, from terminal in psql I enter
>> # ALTER SEQUENCE public.users MINVALUE 9999;
>> But it doesn't  work. I don't think I am specifying the field
>> correctly. the schema is public, the table is users, the field is id.
>
> ALTER SEQUENCE uses the sequence name, not the table name.  Also,
> use RESTART WITH to set the sequence's current value.  Example:
>
> test=> CREATE TABLE users (id serial PRIMARY KEY, username text);
> NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq"
> for "serial" column "users.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "users_pkey" for table "users"
> CREATE TABLE
> test=> ALTER SEQUENCE users_id_seq RESTART WITH 10000;
> ALTER SEQUENCE
> test=> INSERT INTO users (username) VALUES ('Alice');
> INSERT 2592322 1
> test=> INSERT INTO users (username) VALUES ('Bob');
> INSERT 2592323 1
> test=> SELECT * FROM users;
>   id   | username
> -------+----------
>  10000 | Alice
>  10001 | Bob
> (2 rows)
>
> --
> Michael Fuhr