Re: Auto-Increment in Postgres

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