Re: Two questions in a row

Andrew Snow wrote:
>
> > > 1. How do you remove one of two identical rows
> > > from a pgsql table ?
> >
> > DELETE FROM t1 WHERE .... wil do the stuff for you. If you don't know
> > the value into the duplicate field just export the database with pg_dump
> > create
> > a unique index onto this field and reimport all your data. Duplicate
> > data will
> > not be inserted.
>
> Another way could be to
> SELECT oid FROM table WHERE ...;
>
> and then delete based on that. However I believe it is possible to have two
> rows have the same oid but usually that isn't the case.

Its not possible to have two rows with the same oid (unless
you've wrapped around the maximum oid by creating at least 4.2
billion records). So you can eliminate duplicates with the query:

DELETE FROM foo WHERE EXISTS
(SELECT f.key FROM foo f WHERE f.key = foo.key AND f.oid <
foo.oid);

Of course, an index on foo.key will dramatically speed up the
above operation.

Hope that helps,

Mike Mascari