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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
