Re: Update with a Repeating Sequence

The table exists already; all I need to do is update the sequence code
to make the records unique, but also I need each repeating set numbered
from 0 (Zero) so I can select a list of unique farm field records where
seq = 0.

I think that the suggestion to use a cursor sounds good, but I'm
concerned that according to the PG 8.1 documentation, update cursors are
not supported. However, with a read only cursor can I FETCH a row, read
the field variables in it and update one or two of them and have that
change posted back into that record to update the table? I'm not at all
familiar with PostgreSQL cursors yet, so any help on the syntax would be
welcome too. An example script or function showing how to step through
all records and updating just one or two column values in each row would
be appreciated if someone could point me to it.

Again here's the table structure for my small example:
create table farm_fields (
field_id integer,
seq integer
);

And I want to
convert something
like this: to this:

field_id | seq field_id | seq
---------+----- ---------+-----
34556 | 0 34556 | 0
34556 | 0 34556 | 1
34556 | 0 34556 | 2
37000 | 0 37000 | 0
37002 | 0 37002 | 0
37002 | 0 37002 | 1
37005 | 0 37005 | 0

Webb Sprague wrote:
> Untested ideas (beware):
>
> Use an insert trigger that:
> curr_seq := select max(seq) from foo where field_id = NEW.field_id
> if curr_seq is null then NEW.seq := 0
> else NEW.seq := curr_seq + 1
>
> (You have to figure out how to build the trigger infrastructure...)
>
> If you need to do it on a table that is already populated, let us know.
>
> On Tue, Oct 14, 2008 at 9:21 AM, Bill Thoen <bthoen(at)gisnet(dot)com> wrote:
>
>> Grzegorz Jas'kiewicz wrote:
>>
>>> alter table foo add newid sequencial;
>>>
>>> alter table foo drop field_id;
>>>
>>> alter table foo rename newid to field_id;
>>>
>>>
>> I can't do that; I need to preserve the field_id values.
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>
>