Re: Long running update

"Andrew Janian" <ajanian(at)scottrade(dot)com> writes:
> If I cancel the update then I can drop the new column, rename the old
> column, do a vacuum, and then I should be left with what I started with,
> right?

Right.

> How can I expand that column without using this query?

Basically you want to alter the pg_attribute.atttypmod field for the
column. Here's an example:

regression=# create table mytable (mycolumn varchar(10));
CREATE TABLE
regression=# UPDATE pg_attribute SET atttypmod = 25 + 4
regression-# WHERE attrelid = 'mytable'::regclass
regression-# AND attname = 'mycolumn';
UPDATE 1
regression=# \d mytable
Table "public.mytable"
Column | Type | Modifiers
----------+-----------------------+-----------
mycolumn | character varying(25) |

Note the +4 ... this is a hangover from days gone by, but varchar
typmods are still defined as 4 more than what the user said.

*Practice* on a scratch database to make sure you have it right.
Also I'd suggest doing it inside a BEGIN block so you can roll it
back if you mess up. Use \d to verify that the table looks as you
expect before committing.

BTW, this trick doesn't really work nicely for anything except the
case of increasing the field width of a varchar column, so that's
why there's not a cleaner interface for it ...

regards, tom lane