Date: Thu, 10 Nov 2005 10:58:18 -0500
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: how to update table to make dup values distinct
Message-Id: <20051110105818.4dc51e8c.gry@ll.mit.edu>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

[PostgreSQL 7.4RC2 on i686-pc-linux-gnu](I know, I know... must upgrade soon)

I have a table mytable like:
 i |  txt  
---+-------
 1 | the
 2 | the
 3 | rain
 4 | in
 5 | mainly
 6 | spain
 7 | stays
 8 | mainly
 9 | in

I want to update it, adding a ':' to txt so that each txt value is unique.
I don't care which entry gets changed.  I tried:

 update mytable set txt=mytable.txt || ':' from mytable t2 where mytable.txt=t2.txt and mytable.i=t2.i;

but this updated both duplicated entries.  

Um, there may sometimes be 3 or 4 duplicates, not just two.  For these, I can add multiple colons, or one each of an assortment of characters, say ':+*&^#'.

Performance does not matter here.  The real table has 30K rows, ~200 dups.
To clarify, I want to end up with something like:

 1 | the
 2 | the:
 3 | rain
 4 | in
 5 | mainly:
 6 | spain
 7 | stays
 8 | mainly
 9 | in:

-- George
-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)