Обсуждение: 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

On 11 February 2011 11:13, Adarsh Sharma <adarsh.sharma@orkash.com> 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.

If it's your primary key, changing the values every time you remove
anything isn't really treating it as a key so much as ranking them in
order of insertion.

Why do you want to reset the sequence?  If it's a primary key, it
shouldn't matter.

But if you really insist, you can do this:

ALTER SEQUENCE meta_test_metadataid_seq RESTART WITH 1;

UPDATE TABLE meta_test set metadataid = nextval('meta_test_metadataid_seq');

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

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