Re: UPDATE many records

On 1/7/20 12:47 PM, Israel Brewster wrote:
> One potential issue I just thought of with this approach: disk space.
> Will I be doubling the amount of space used while both tables exist? If
> so, that would prevent this from working - I don’t have that much space
> available at the moment.

It will definitely increase the disk space by at least the data in the
new table. How much relative to the old table is going to depend on how
aggressive the AUTOVACUUM/VACUUM is.

A suggestion for an alternative approach:

1) Create a table:

create table change_table(id int, changed_fld some_type)

where is is the PK from the existing table.

2) Run your conversion function against existing table with change to
have it put new field value in change_table keyed to id/PK. Probably do
this in batches.

3) Once all the values have been updated, do an UPDATE set changed_field
= changed_fld from change_table where existing_table.pk = change_table.id;

> ---
> Israel Brewster
> Software Engineer
> Alaska Volcano Observatory
> Geophysical Institute - UAF
> 2156 Koyukuk Drive
> Fairbanks AK 99775-7320
> Work: 907-474-5172
> cell:  907-328-9145

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com