Skip site navigation (1) Skip section navigation (2)

Re: altering a table to set serial function

From: "Scott Marlowe" <smarlowe(at)qwest(dot)net>
To: "Prabu Subroto" <prabu_subroto(at)yahoo(dot)com>
Cc: "Postgres General Milis" <pgsql-general(at)postgresql(dot)org>
Subject: Re: altering a table to set serial function
Date: 2004-07-28 04:37:17
Message-ID: 1090989437.6263.9.camel@localhost.localdomain (view raw, whole thread or download thread mbox)
Lists: 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 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

est=> create table test (id serial primary key, info text);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for
"serial" column ""
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
test=> \d test
                           Table "public.test"
 Column |  Type   |                      Modifiers
 id     | integer | not null default nextval('public.test_id_seq'::text)
 info   | text    |
    "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);

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"
test=> create sequence test_id_seq;
test=> alter table test alter column id set default

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;
test=> select * from test;
 info | id
 abc  |  1
 def  |  2
(2 rows)


And there you go!

In response to


pgsql-general by date

Next:From: Mike NolanDate: 2004-07-28 07:32:19
Subject: Re: Before/After trigger sequencing questiont
Previous:From: Tom LaneDate: 2004-07-28 04:36:23
Subject: Re: Before/After trigger sequencing question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group