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
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?
__________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail