Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Alvaro Herrera
Alvaro Herrera wrote: > Lincoln Yeoh wrote: > > At 06:32 PM 9/6/2007, Richard Huxton wrote: > > > >> Two other tips for bulk-updates like this: > >> 1. Do as many columns in one go as you can > >> 2. Only update rows that need updating > >> > >> When you've finished, a CLUSTER/VACUUM FULL can be us

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Alvaro Herrera
Lincoln Yeoh wrote: > At 06:32 PM 9/6/2007, Richard Huxton wrote: > >> Two other tips for bulk-updates like this: >> 1. Do as many columns in one go as you can >> 2. Only update rows that need updating >> >> When you've finished, a CLUSTER/VACUUM FULL can be useful too. > > How about: make sure you

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Lincoln Yeoh
At 06:32 PM 9/6/2007, Richard Huxton wrote: Two other tips for bulk-updates like this: 1. Do as many columns in one go as you can 2. Only update rows that need updating When you've finished, a CLUSTER/VACUUM FULL can be useful too. How about: make sure you have enough free space because the t

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread hubert depesz lubaczewski
On Thu, Sep 06, 2007 at 01:39:51PM +0200, Nis Jørgensen wrote: > Rubbish. From the documentation: hmm .. i'm sorry - i was *sure* about it because we were bitten by something like this lately - apparently it was similiar but not the same. sorry again for misinformation. depesz -- quicksil1er:

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Nis Jørgensen
hubert depesz lubaczewski skrev: > On Thu, Sep 06, 2007 at 11:08:02AM +0200, Alban Hertroys wrote: >> create index tmp_idx on table(number) where number != trim(number); >> analyze table; >> update table set number = trim(number) where number != trim(number); > > dont use !=. use <>. != does somet

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread hubert depesz lubaczewski
On Thu, Sep 06, 2007 at 11:08:02AM +0200, Alban Hertroys wrote: > create index tmp_idx on table(number) where number != trim(number); > analyze table; > update table set number = trim(number) where number != trim(number); dont use !=. use <>. != does something different, and in fact it is not a re

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Richard Huxton
Ow Mun Heng wrote: On Thu, 2007-09-06 at 04:47 -0500, Ron Johnson wrote: Maybe there's an English language "issue", or maybe I'm just excessively picky, but using "number" in this context is confusing. My Bad.. hehe.. Then I agree with Alban: update table set number = trim(number); or, if

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Ow Mun Heng
On Thu, 2007-09-06 at 04:47 -0500, Ron Johnson wrote: > Maybe there's an English language "issue", or maybe I'm just > excessively picky, but using "number" in this context is confusing. My Bad.. hehe.. > Then I agree with Alban: > update table set number = trim(number); > or, if you need the

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Ron Johnson
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 04:20, Ow Mun Heng wrote: > On Thu, 2007-09-06 at 04:07 -0500, Ron Johnson wrote: > >> On 09/06/07 01:13, Ow Mun Heng wrote: > >>> update org_column set number = foo.number where foo.unique_id = >>> org_column=unique_id. >> Number? Where

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Ow Mun Heng
On Thu, 2007-09-06 at 11:08 +0200, Alban Hertroys wrote: > Ow Mun Heng wrote: > > I found 2 new ways to do this. > > > > option 1 > > --- > > > > create table foo as select unique_id, rtrim(number) as number from foo; > > alter table add primary key... > > create index... > > drop org_table >

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Ow Mun Heng
On Thu, 2007-09-06 at 04:07 -0500, Ron Johnson wrote: > On 09/06/07 01:13, Ow Mun Heng wrote: > > update org_column set number = foo.number where foo.unique_id = > > org_column=unique_id. > > Number? Where does "number" come from? Unless you've got weird > field names, that doesn't sound like

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Ron Johnson
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 01:13, Ow Mun Heng wrote: > I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using > chopblanks) and have ended up with a column where the "space" is being > interpreted as a value. > > eg: > > "ABC " when it should be "A

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-06 Thread Alban Hertroys
Ow Mun Heng wrote: > I found 2 new ways to do this. > > option 1 > --- > > create table foo as select unique_id, rtrim(number) as number from foo; > alter table add primary key... > create index... > drop org_table > alter table rename... > All this is ~10min This only works if you don't hav

Re: [GENERAL] Need suggestion on how best to update 3 million rows

2007-09-05 Thread Ow Mun Heng
On Thu, 2007-09-06 at 14:13 +0800, Ow Mun Heng wrote: > I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using > chopblanks) and have ended up with a column where the "space" is being > interpreted as a value. > > eg: > > "ABC " when it should be "ABC" > > this is being defined

[GENERAL] Need suggestion on how best to update 3 million rows

2007-09-05 Thread Ow Mun Heng
I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using chopblanks) and have ended up with a column where the "space" is being interpreted as a value. eg: "ABC " when it should be "ABC" this is being defined as varchar(4) I've already pull the relevent columns with create foo