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 [EMAIL PROTECTED] 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
