Re: altering a starting value of "serial" macro - Mailing list pgsql-general

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
>>>
>>>
>>>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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings