Обсуждение: Update with a Repeating Sequence

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?

TIA,
- Bill Thoen

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.

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@gisnet.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

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@gisnet.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>
>

On Oct 14, 2008, at 11:36 AM, Bill Thoen wrote:

> 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.

"select distinct on (field_id) * from table;" should let you do that,
without requiring the repeating set stuff.

Cheers,
   Steve

Steve Atkins wrote:
>
> 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 ...
Thanks, but in this case I really need both unique records and a
repeated sequence so I can select the first occurrence of each record
(i.e. WHERE seq =0) and sometimes I need the max(seq) for particular
records. Since this is a read-only table, maintaining it is not a
problem.  Anyway, I did mange to solve it. Here's a little test script
that shows how:

CREATE TABLE test (
   field_id integer,
   seq integer
);
INSERT INTO test VALUES (1, 0);
INSERT INTO test VALUES (2, 0);
INSERT INTO test VALUES (3, 0);
INSERT INTO test VALUES (3, 0);
INSERT INTO test VALUES (3, 0);
INSERT INTO test VALUES (4, 0);
INSERT INTO test VALUES (4, 0);
INSERT INTO test VALUES (5, 0);
INSERT INTO test VALUES (6, 0);

-- Create table to hold static variables
CREATE TABLE tmp (last_id integer, cnt integer);
INSERT INTO tmp VALUES(0,0);

-- Function to fill in repeated sequence
CREATE OR REPLACE FUNCTION test_it (field_id integer) RETURNS integer AS $$
DECLARE
   r tmp%ROWTYPE;
   nLast_id integer;
   nCnt integer;
   BEGIN
      SELECT * INTO r FROM tmp;
      nLast_id = r.last_id;
      nCnt = r.cnt;
      IF field_id = nLast_id THEN
         nCnt = nCnt + 1;
      ELSE
         nCnt = 0;
         nLast_id = field_id;
      END IF;
      UPDATE tmp SET last_id=nLast_id, cnt=nCnt;
      RETURN nCnt;
   END;
$$ LANGUAGE plpgsql;

-- Fill in repeated sequence
UPDATE test SET seq=test_it (field_id);

-- Show results
SELECT * FROM test ORDER BY field_id, seq;

-- Clean up
DROP FUNCTION test_it(integer);
DROP TABLE tmp;
DROP TABLE test;

Bill Thoen wrote:
> Steve Atkins wrote:
>>
>> 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 ...

I know its academic now. But this is a great use case for the windowing
functions being added to 8.4. In 8.4 it should be as easy as

SELECT field_id, RANK() OVER(PARTITION BY field_id) AS seq
FROM foo;


Artacus