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

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.