Обсуждение: 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
alter table foo add newid sequencial;
alter table foo drop field_id;
alter table foo rename newid to field_id;
oh, sorry - you want something else. blah.
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.
I would probably do that in plpgsql, as a cursor
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