Re: altering a table to set serial function - Mailing list pgsql-general

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