Re: altering a starting value of "serial" macro in postgres

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 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 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