Thread: altering a table to set serial function

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
"

Please tell me the correct command to that.

Thank you very much in advance.



__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

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!

create table foo
(
    salesid serial primary key
);

    serial is a type thus you can't say "serial int4". serial already implies
integer. bigserial implies bigint. primary key tells postgres to create
the appropriate index.



On Tue, 27 Jul 2004 10:16:11 -0700 (PDT), Prabu Subroto
<prabu_subroto@yahoo.com> 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
> "
>
> Please tell me the correct command to that.
>
> Thank you very much in advance.
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - Helps protect you from nasty viruses.
> http://promotions.yahoo.com/new_mail
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

> 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

    I'd add UNIQUE NOT NULL or PRIMARY KEY just in case some UPDATE tries to
modify the id field to NULL which would be a Very Bad Thing to do.

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

On Wed, Jul 28, 2004 at 05:09:33AM -0700, Prabu Subroto wrote:
> Dear Scott...
>
> My God.... so I can not use "alter table" to define a
> column with int data type?

eh? Sure you can:

alter table x add column y integer;

What's he's saying is that the "serial" shortcut isn't there and
proceeded to tell you how to do it manually...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Prabu Subroto <prabu_subroto@yahoo.com> writes:

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

You can do it "by hand" without dropping the column:

CREATE SEQUENCE salesid_seq;
SELECT setval('salesid_seq', (SELECT max(salesid) FROM sales) + 1);
ALTER TABLE sales ALTER COLUMN salesid DEFAULT nextval('salesid_seq');

This is the same thing that the SERIAL datatype does "behind the
scenes".

I can't vouch for the exact syntax of the above but that should get
you started.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

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

This is exactly what I need..

Thank you very much for your kindness, Doug.

Thank you...thank you...very....very,,, much.
--- Doug McNaught <doug@mcnaught.org> wrote:
> Prabu Subroto <prabu_subroto@yahoo.com> writes:
>
> > 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?
>
> You can do it "by hand" without dropping the column:
>
> CREATE SEQUENCE salesid_seq;
> SELECT setval('salesid_seq', (SELECT max(salesid)
> FROM sales) + 1);
> ALTER TABLE sales ALTER COLUMN salesid DEFAULT
> nextval('salesid_seq');
>
> This is the same thing that the SERIAL datatype does
> "behind the
> scenes".
>
> I can't vouch for the exact syntax of the above but
> that should get
> you started.
>
> -Doug
> --
> Let us cross over the river, and rest under the
> shade of the trees.
>    --T. J. Jackson, 1863
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

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

It's solved.

Thank you very much for your kindness.
--- John Sidney-Woollett <johnsw@wardbrook.com> wrote:
> 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
> >>>
>
=== message truncated ===




__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail

On Wed, 2004-07-28 at 06:09, Prabu Subroto wrote:
> Dear Scott...
>
> My God.... so I can not use "alter table" to define a
> column with int data type?

Not define, REdefine.  Right now, the version going into beta will let
you redefine columns from one type to another.  Til then, you have to
make a new column, and move your data into it.:

alter table test add column newid;
update test set newid=cast (id as int4);

Then the rest of what I posted.

On Wed, 2004-07-28 at 06:09, 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.

As a followup, I thought you should know that in MySQL (on my box I'm
running 3.23.58) if you do the following, you get some unintended
consequences:

mysql> create table test (id varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('123');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('a001');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('001a');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| id   |
+------+
| 123  |
| abc  |
| a001 |
| 001a |
+------+
4 rows in set (0.01 sec)
mysql> alter table test modify id int4;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 3

mysql> select * from test;
+------+
| id   |
+------+
|  123 |
|    0 |
|    0 |
|    1 |
+------+
4 rows in set (0.00 sec)

Notice that 123 and 001a got converted.  abc and a001 got plain
dropped.  If you needed the data in that column, it's now gone.  If you
change the column back to varchar(10) the data is still gone.  No error,
so no chance to abort the change.

In PostgreSQL EVERYTHING is transactable:  For instance:

test=> create table test (id serial primary key, info text);
test=> insert into test values (DEFAULT,'abc');
test=> insert into test values (DEFAULT,'test row');
test=> begin;
test=> alter table test drop column info;
test=> alter table test add column otherinfo text;
test=> \d test
                            Table "public.test"
  Column   |  Type   |                      Modifiers
-----------+---------+------------------------------------------------------
 id        | integer | not null default
nextval('public.test_id_seq'::text)
 otherinfo | text    |
Indexes:
    "test_pkey" primary key, btree (id)
test=> rollback;
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)

Notice the changes are rolled back and the data is maintained in that
table, no losses.

So, the effort required in "doing it right" in PostgreSQL is even
higher, because any kind of alter column statement needs to be
transactable.  In fact, the only non-transactable DDL/DML in PostgreSQL
is create / drop database, since transactions by their nature exist
within a database.

So, while MySQL may have happily followed your commands, it also might
have scrammed your data.  PostgreSQL tends to err on the side of
caution, so even when this feature becomes available, it will error out
when trying to alter a column where the values don't fit, unless there's
a cascade or ignore keyword to tell it to go ahead anyway.  And trust
me, if you've got important data, it's the way you want your database to
behave.