On Mon, Mar 5, 2012 at 2:43 PM, DrYSG <ygutfreund(at)draper(dot)com> wrote:
> One point I might not have made clear. The reason I want to remove duplicates
> is that the column "data_object.unique_id" became non-unique (someone added
> duplicate rows). So I added the bigSeriel (idx) to uniquely identify the
> rows, and I was using the SELECT MIN(idx) and GroupBy to pick just one of
> the rows that became duplicated.
> I am going to try out some of your excellent suggestions. I will report back
> on how they are working.
> One idea that was given to me was the following (what do you think Merlin?)
> CREATE TABLE portal.new_metatdata AS
> select distinct on (data_object.unique_id) * FROM portal.metadata;
sure that will work, but as Michael noted it's not always practical to
do that. Also, if a fairly small percentage of the records have to be
deleted, an in-place delete may end up being faster anyways. Modern
postgres is pretty smart at optimizing 'where exists' and you should
get a decent plan.
In response to
pgsql-novice by date
|Next:||From: Steve Crawford||Date: 2012-03-06 23:55:08|
|Subject: Re: Database not browsable during COPY on PostgreSQL|
|Previous:||From: Arda Çeşmecioğlu||Date: 2012-03-06 20:56:55|
|Subject: more than just (m:n)?|