Re: altering a starting value of "serial" macro - Mailing list pgsql-general
From | Prabu Subroto |
---|---|
Subject | Re: altering a starting value of "serial" macro |
Date | |
Msg-id | 20040728141350.30373.qmail@web41806.mail.yahoo.com Whole thread Raw |
In response to | Re: altering a starting value of "serial" macro (John Sidney-Woollett <johnsw@wardbrook.com>) |
List | pgsql-general |
It's solved. Thank you very much for your kindness. --- John Sidney-Woollett <johnsw@wardbrook.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@yahoo.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@qwest.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
pgsql-general by date: