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.

Quassnoi · Accepted Answer · 2009-07-28 18:11:23Z

1

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.

edited Jul 28, 2009 at 18:11

answered Jul 28, 2009 at 18:06

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Hart Over a year ago

Thanks, the ctid thing was the missing part of the puzzle for me!

Christopher · Accepted Answer · 2009-07-28 18:00:19Z

0

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

answered Jul 28, 2009 at 18:00

Christopher

9,1242 gold badges35 silver badges43 bronze badges

Collectives™ on Stack Overflow

Postgres add seq# to distinguish unique rows in the database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related