Re: Smartest way to resize a column?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Adrian Klaver <aklaver(at)comcast(dot)net>
Cc: "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com>, "Ian Barwick" <barwick(at)gmail(dot)com>, "PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Smartest way to resize a column?
Date: 2009-01-12 05:02:36
Message-ID: 13218.1231736556@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Adrian Klaver <aklaver(at)comcast(dot)net> writes:
> On Sunday 11 January 2009 5:21:46 pm Phoenix Kiula wrote:
>> On Mon, Jan 12, 2009 at 9:12 AM, Ian Barwick <barwick(at)gmail(dot)com> wrote:
>>> 2009/1/12 Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>:
>>>> I am trying to resize a column on a large-ish database (with 5 million
>>>> rows).
>>>
>>> ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(35)
>>>
>> Also, is there a safe and fast way of doing this on a live database,
>> without bringing it down if possible? This is an indexed column so I
>> wonder if that will slow up the process quite a bit?

> From the fine manual:
> http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html

> "Adding a column with a non-null default or changing the type of an existing
> column will require the entire table to be rewritten. This might take a
> significant amount of time for a large table; and it will temporarily require
> double the disk space."

ALTER COLUMN TYPE is intended for cases where actual transformation of
the data is involved. Obviously varchar(20) to varchar(35) doesn't
really require any per-row effort, but there's no operation in the
system that handles that case. But if you're brave, you can do it
via manipulation of the system catalogs. Observe:

regression=# create table t1(f1 varchar(20));
CREATE TABLE
regression=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
f1 | character varying(20) |

regression=# select atttypmod from pg_attribute where attrelid = 't1'::regclass and attname = 'f1';
atttypmod
-----------
24
(1 row)

regression=# update pg_attribute set atttypmod = 35+4 where attrelid = 't1'::regclass and attname = 'f1';
UPDATE 1
regression=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
f1 | character varying(35) |

(Why the +4 you ask? It's historical :-()

Recommendations:

1. Practice on a scratch database to make sure it will work the way
you want.

2. Do the deed inside a BEGIN block so you can roll it back if
subsequent checking (at least a \d check) doesn't look right.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Phoenix Kiula 2009-01-12 06:42:55 Re: Smartest way to resize a column?
Previous Message Tim Hart 2009-01-12 03:35:14 Re: Unexpected behavior from psql