Postgres add seq# to distinguish unique rows in the database

Question

In an existing application, I have a table which has no primary key, which (rarely) has duplicate rows in it. For integration with another system, I need to add a column to the table that makes these duplicate rows unique. The table is essentially:

+------+---------+--------+
| txn# | detail# | amount |
+------+---------+--------+

I could just use an incrementing seq# for every existing row, however, the application changes will be to take the existing 'almost key' (basically transaction# detail#), and increment the sequence number for this combination (so, e.g., I'll have two rows for txn# 1, detail# 1, and have seq# 1 for the first and seq#2 for the second, and if txn#513, detail# 44 has 3 duplicate rows, these would have seq# 1,2,3 appropriately. It would be ideal (from a support perspective), if the data pre- and post- application changes was similarly set out. Is there some easy way to create this column, or do I need to cycle through the rows manually, resetting the sequence used every time the txn# or detail# changes?

edited to add: the app is deployed on both Postgresql 7.4 & 8.1, and the solution needs to work for both versions.

Answer 1

You could alter the table to create your new primary key column. Then you could create an insert and update trigger on that table that combines the two columns and inserts them into the right row.

http://www.postgresql.org/docs/8.4/static/triggers.html

Answer 2

If your columns are "almost unique", i. e. there are no many duplicates of each value, you can use this syntax:

ALTER TABLE mytable ADD seq INT;

UPDATE  mytable mo
SET     seq = 
        (
        SELECT  COUNT(*)
        FROM    mytable mi
        WHERE   mi.txn = mo.txn
                AND mi.detail = mo.detail
                AND mi.ctid <= mo.ctid
        )

ALTER TABLE mytable ALTER COLUMN seq SET NOT NULL;

ALTER TABLE mytable DROP CONSTRAINT t_mytable_pkey; -- the name of your current primary key

ALTER TABLE mytable ADD CONSTRAINT t_mytable_pkey PRIMARY KEY (txn, detail, seq);

This will complete fast, if there are no too many duplicates.