Re: Auto-Increment in Postgres

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