Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
> > alter table rename...
> > All this is ~10min
> 
> This only works if you don't have foreign key constraints involving that
> table. Otherwise you just lost your data integrity (although I expect an
> error to be thrown).

Got it.. Don't use FK's so.. I'm safe (for now)
> 
> > option 2
> > ========
> > This I saw in the mysql archives (in my laptop).. when I say this I
> > went.. WTF? This is possible?? Dang IT! 
> > 
> > update a set number=replace(number,'ABC ', 'ABC') where reptest like '%
> > ABC%';
> 
> Ehrm... yes, nothing special about it. Basic SQL really ;)
Hmm.. I feel the salt..

> But shouldn't you be using trim() or rtrim() instead?:
> 
> update table set number = trim(number)

Hmm.. didn't think of that. Next time I guess. (in all honestly, I
didn't know you can update it on the same process/column/table. I was
dumping it to a separate table and updating it..

Now I know..
> 
> you could probably speed that up by only querying the records that need
> trimming, for example:
> 
> create index tmp_idx on table(number) where number != trim(number);
> analyze table;
> update table set number = trim(number) where number != trim(number);

all fields in that column is affected. I have "     " (5 spaces) instead
of nulls

Thanks for the pointers..

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux