altering a table to set serial function

[Prabu Subroto]

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

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Pierre-Frédéric Caillaud]

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
<pr***********@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Pierre-Frédéric Caillaud]

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Prabu Subroto]

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 <sm******@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

[Martijn van Oosterhout]

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 <kl*****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBB6Z7Y5Twig3Ge+YRAiS+AJ9jmOjEMpGMjNYlvMtAxB dpeY4gnACgwT5u
VQ80cAufd+g3x02x8L+aq5o=
=I18U
-----END PGP SIGNATURE-----

[Doug McNaught]

Prabu Subroto <pr***********@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Prabu Subroto]

This is exactly what I need..

Thank you very much for your kindness, Doug.

Thank you...thank you...very....very,,, much.
--- Doug McNaught <do**@mcnaught.org> wrote:

Prabu Subroto <pr***********@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Prabu Subroto]

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 <pr***********@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 <sm******@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

[John Sidney-Woollett]

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 <pr***********@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 <sm******@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Prabu Subroto]

It's solved.

Thank you very much for your kindness.
--- John Sidney-Woollett <jo****@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 <pr***********@yahoo.com> wrote:

Dear Scott...

My God.... so I can not use "alter table" to definea
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). thetable has already contented the data.

I built an application with Qt. I thougt that I candefine 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 dropthe 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 <sm******@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 ofthat 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Scott Marlowe]

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.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Scott Marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match