Re: altering a starting value of "serial" macro

OK I did it :
create sequence sales_salesid_seq;
alter table sales alter column salesid set default
nextval('sales_salesid_seq');

but a new problem comes, because the table "sales" is
not empty. if the sequence counter reach a value that
already exists in the table "sales" than of course
comes this error message :
"
kv=# insert into sales (firstname) values ('baru5');
ERROR: duplicate key violates unique constraint
"sales_pkey"
"

so now I think the only one solution is to set the
starting counter for the "serial" macro, for instance
to : "501" (the maximum current values of column
salesid is 500).

Anybody has a solution?

Thank you very much in advance.
--- Prabu Subroto <prabu_subroto(at)yahoo(dot)com> wrote:
> Dear Scott...
>
> My God.... so I can not use "alter table" to define
> a
> column with int data type?
>
> Here is the detail condition:
> I have created a table "sales". And I forgot to
> define
> auto_increment for primary key "salesid" (int4). the
> table has already contented the data.
>
> I built an application with Qt. I thougt that I can
> define a column with auto_increment function
> afterall.
>
> I want my application program only has to insert
> "firstname", "lastname" etc. And the database server
> (postgres) will put the increment value into the
> salesid automatically.
>
> If I read your suggestion, that means...I have drop
> the column "salesid" and re-create the column
> "salesid". and it means, I will the data in the
> current "salesid" column.
>
> Do you have further suggestion?
>
> Thank you very much in advance.
> --- Scott Marlowe <smarlowe(at)qwest(dot)net> wrote:
> > On Tue, 2004-07-27 at 11:16, Prabu Subroto wrote:
> > > Dear my friends...
> > >
> > > I am using postgres 7.4 and SuSE 9.1.
> > >
> > > I want to use auto_increment as on MySQL. I look
> > up
> > > the documentation on www.postgres.com and I
> found
> > > "serial" .
> > >
> > > But I don't know how to create auto_increment.
> > > here is my try:
> > > "
> > > kv=# alter table sales alter column salesid int4
> > > serial;
> > > ERROR: syntax error at or near "int4" at
> > character 40
> > > "
> >
> > Serial is a "macro" that makes postgresql do a
> > couple of things all at
> > once. Let's take a look at the important parts of
> > that by running a
> > create table with a serial keyword, and then
> > examining the table, shall
> > we?
> >
> > est=> create table test (id serial primary key,
> info
> > text);
> > NOTICE: CREATE TABLE will create implicit
> sequence
> > "test_id_seq" for
> > "serial" column "test.id"
> > NOTICE: CREATE TABLE / PRIMARY KEY will create
> > implicit index
> > "test_pkey" for table "test"
> > CREATE TABLE
> > test=> \d test
> > Table "public.test"
> > Column | Type | Modifiers
> >
>
--------+---------+------------------------------------------------------
> > id | integer | not null default
> > nextval('public.test_id_seq'::text)
> > info | text |
> > Indexes:
> > "test_pkey" primary key, btree (id)
> >
> > test=> \ds
> > List of relations
> > Schema | Name | Type | Owner
> > --------+-------------+----------+----------
> > public | test_id_seq | sequence | smarlowe
> > (1 row)
> >
> > Now, as well as creating the table and sequence,
> > postgresql has, in the
> > background, created a dependency for the sequence
> on
> > the table. This
> > means that if we drop the table, the sequence
> > created by the create
> > table statement will disappear as well.
> >
> > Now, you were close, first you need to add a
> column
> > of the proper type,
> > create a sequence and tell the table to use that
> > sequence as the
> > default. Let's assume I'd made the table test
> like
> > this:
> >
> > test=> create table test (info text);
> > CREATE TABLE
> > test=>
> >
> > And now I want to add an auto incrementing column.
>
> > We can't just add a
> > serial because postgresql doesn't support setting
> > defaults in an alter
> > table, so we just add an int4, make a sequence,
> and
> > assign the default:
> >
> > test=> alter table test add id int4 unique;
> > NOTICE: ALTER TABLE / ADD UNIQUE will create
> > implicit index
> > "test_id_key" for table "test"
> > ALTER TABLE
> > test=> create sequence test_id_seq;
> > CREATE SEQUENCE
> > test=> alter table test alter column id set
> default
> > nextval('test_id_seq'::text);
> > ALTER TABLE
> >
> >
> > Now, if you have a bunch of already existing rows,
> > like this:
> >
> > test=> select * from test;
> > info | id
> > ------+----
> > abc |
> > def |
> > (2 rows)
> >
> > then you need to populate those rows id field to
> put
> > in a sequence, and
> > that's pretty easy, actually:
> >
> > est=> update test set id=DEFAULT;
> > UPDATE 2
> > test=> select * from test;
> > info | id
> > ------+----
> > abc | 1
> > def | 2
> > (2 rows)
> >
> > test=>
> >
> > And there you go!
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Y! Messenger - Communicate in real time. Download
> now.
> http://messenger.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail