Re: Auto-Increment in Postgres

[Fredric Fredricson <Fredric.Fredricson@xxxxxxxxxxxxx>]

On 02/11/2011 12:13 PM, Adarsh Sharma wrote:

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

1. The SERIAL data type will not give you what you want. SERIAL will increment by one for each INSERT regardless of the content of you table (and, specifically, the value of you "metadataid" field). SERIAL does not check for unused ids, if you need this you should probably write trigger procedure to handle that.

2. SERIAL does not make the field unique, to do that use "SERIAL UNIQUE".

3. ALTER TABLE tablename ADD PRIMARY KEY metadataid ;

4. Once you added a serial with ALTER TABLE a sequence is created that start counting on 1. The next insert will add a row with metadataid = 1. If you have a UNIQUE or PRIMARY KEY constraint this will fail if there already is a row with metadataid set to 1. SERIAL has no magic that handle this.

Useful resources:
http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL
http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html
http://www.postgresql.org/docs/8.4/interactive/sql-altertable.html

/Fredric

begin:vcard
fn:Fredric Fredricson
n:Fredricson;Fredric
org:Ln4 Solutions AB
email;internet:Fredric.Fredricson@xxxxxxxxxxxxx
title:CTO
tel;home:+46 8 91 64 39
tel;cell:+46 70 677 58 48
version:2.1
end:vcard

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general