Re: Auto-Increment in Postgres

"But this adds the column at the end." - column order in the table
definition is unreliable; when you output you should specify the column
order yourself.

As to populating within a sequence that has missing values that is more
trouble than it is worth.

If you want to add a column and make it the primary key you need to indicate
such in your ALTER TABLE statement by adding "PRIMARY KEY" to the end of it:
"ALTER TABLE t ADD COLUMN c serial PRIMARY KEY"

If you really feel you need to do as you explain I would advise giving more
context as to what and why you are doing that so alternative solutions can
be presented.

David J

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Adarsh Sharma
Sent: Friday, February 11, 2011 6:14 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Auto-Increment in Postgres

Dear all,

I have an Integer column in Postgres database table. Let's say the column
has below values :

1
2
3
4
5
6
7
8
9
10

Now if i deleted some rows where id= 3 ,5 and 8 or it have these type of
data then

The data look like as :

1
2
4
6
7
9
10

I want to have it id's as
1
2
3
4
5
6
7
and next data is inserted right at 8 .

I follow these steps

alter table meta_test drop column metadataid;

alter table meta_test add metadataid serial;

But this adds the column at the end but i want to it as primary key.

Please help how to achieve it.

Thanks & best Regards
Adarsh Sharma