To: pgsql-sql@postgresql.org
From: Harald Fuchs <hf0923x@protecting.net>
Subject: Re: how to update table to make dup values distinct
Date: 11 Nov 2005 15:38:49 +0100
Organization: Linux Private Site
Lines: 47
Message-ID: <871x1n5jwm.fsf@srv.protecting.net>
References: <20051110105818.4dc51e8c.gry@ll.mit.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7

In article <20051110105818.4dc51e8c.gry@ll.mit.edu>,
george young <gry@ll.mit.edu> writes:

> [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:

Try the following:

  UPDATE mytable
  SET txt = txt || substring ('::::::::::::::::' for (
      SELECT count(*)
      FROM mytable t1
      WHERE t1.txt = mytable.txt AND t1.i < mytable.i
    )::int)