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
