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

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!