Re: altering a starting value of "serial" macro

It's solved.

Thank you very much for your kindness.
--- John Sidney-Woollett <johnsw(at)wardbrook(dot)com> wrote:
> You missed the command:
>
> SELECT setval('salesid_seq', (SELECT max(salesid)
> FROM sales) + 1);
>
> John Sidney-Woollett
>
> Prabu Subroto wrote:
>
> > 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
> >>>
>
=== message truncated ===

__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail