Re: Update with a Repeating Sequence

On Oct 14, 2008, at 9:04 AM, Bill Thoen wrote:

> I've got a table with repeated records that I want to make unique by
> adding a sequence code of 0,1,2,...,n for each set of repeated
> records. Basically, I want to turn:
> field_id | seq
> ----------+-----
> 1 | 0
> 2 | 0
> 3 | 0
> 3 | 0
> 3 | 0
> 4 | 0
> 4 | 0
> 5 | 0
> 6 | 0
> into:
> field_id | seq
> ----------+-----
> 1 | 0
> 2 | 0
> 3 | 0
> 3 | 1
> 3 | 2
> 4 | 0
> 4 | 1
> 5 | 0
> 6 | 0
>
> What's the best way to that?

This is mildly tricky to do, and hard to maintain.

In most cases where people say they need this, they're actually
perfectly happy with the seq value being enough to make the row
unique, and ideally increasing in order of something such as insertion
time ...

field_id | seq
----------+-----
1 | 0
2 | 1
3 | 2
3 | 3
3 | 4
4 | 5
4 | 6
5 | 7
6 | 8

... which is trivial to do with a sequence.

Cheers,
Steve