Thread: Smartest way to resize a column?
I am trying to resize a column on a large-ish database (with 5 million rows). The column was 20 characters before, now I want to make it 35 characters. Challenge is: this is the main indexed column in a busy database. I tried looking at the ALTER TABLE commands available and there seems nothing that allows me to simply change column size from varchar(20) to varchar(35)? So I have this in mind: BEGIN; ALTER TABLE users ADD COLUMN name_new varchar(35); UPDATE users SET name_new = name; ALTER TABLE users DROP COLUMN name; ALTER TABLE users RENAME COLUMN name_new TO name; COMMIT; I guess this would work, but I am wondering if there is a nicer way to do this that doesn't involve a new column, copying, then dropping old column? Thanks!
2009/1/12 Phoenix Kiula <phoenix.kiula@gmail.com>: > I am trying to resize a column on a large-ish database (with 5 million rows). > > The column was 20 characters before, now I want to make it 35 characters. > > Challenge is: this is the main indexed column in a busy database. > > I tried looking at the ALTER TABLE commands available and there seems > nothing that allows me to simply change column size from varchar(20) > to varchar(35)? The syntax you want (at least in more recent PostgreSQL versions, you don't mention which version you have) is: ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(35) HTH Ian Barwick
On Sunday 11 January 2009 5:07:31 pm Phoenix Kiula wrote: > I am trying to resize a column on a large-ish database (with 5 million > rows). > > The column was 20 characters before, now I want to make it 35 characters. > > Challenge is: this is the main indexed column in a busy database. > > I tried looking at the ALTER TABLE commands available and there seems > nothing that allows me to simply change column size from varchar(20) > to varchar(35)? > > So I have this in mind: > > > BEGIN; > ALTER TABLE users ADD COLUMN name_new varchar(35); > UPDATE users SET name_new = name; > ALTER TABLE users DROP COLUMN name; > ALTER TABLE users RENAME COLUMN name_new TO name; > COMMIT; > > > I guess this would work, but I am wondering if there is a nicer way to > do this that doesn't involve a new column, copying, then dropping old > column? > > Thanks! ALTER TABLE users ALTER COLUMN name_new TYPE varchar(35) -- Adrian Klaver aklaver@comcast.net
On Mon, Jan 12, 2009 at 9:12 AM, Ian Barwick <barwick@gmail.com> wrote: > 2009/1/12 Phoenix Kiula <phoenix.kiula@gmail.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) > > HTH > Thanks! I guess I was missing the word "TYPE". This won't affect my "not null" on the column right? 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?
On Sunday 11 January 2009 5:21:46 pm Phoenix Kiula wrote: > On Mon, Jan 12, 2009 at 9:12 AM, Ian Barwick <barwick@gmail.com> wrote: > > 2009/1/12 Phoenix Kiula <phoenix.kiula@gmail.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) > > > > HTH > > Thanks! I guess I was missing the word "TYPE". This won't affect my > "not null" on the column right? > > 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." -- Adrian Klaver aklaver@comcast.net
Adrian Klaver <aklaver@comcast.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@gmail.com> wrote: >>> 2009/1/12 Phoenix Kiula <phoenix.kiula@gmail.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
> 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: ...snip.... Thanks Tom. Yes, no data change is required. But I don't think I have the understanding to try what you suggested. Running the alter table on my home database (almost same data) took an hour and it wasn't complete. I had even dropped the index. So I guess altering it that way is a pain in PGSQL. Bummer.
On Mon, Jan 12, 2009 at 1:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: .....<snip>.... > 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 :-() > Tom, this has worked, and a "\d TABLENAME" shows that the column is varchar(35). But I still have messages in my log saying: --- ERROR: value too long for type character varying(20) --- Why is this? There are no other varchar(20) columns in my DB at all, no other table. Only this column used to be 20 characters and using your command I changed it to 35. It looks alright, the column also accepts the value, but the error is logged. I'm confused. How to stop the error? Thanks
Phoenix Kiula <phoenix.kiula@gmail.com> writes:
> Tom, this has worked, and a "\d TABLENAME" shows that the column is
> varchar(35).
> But I still have messages in my log saying:
> ERROR: value too long for type character varying(20)
Cached plans maybe?
regards, tom lane
On Tue, Feb 3, 2009 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Phoenix Kiula <phoenix.kiula@gmail.com> writes: >> Tom, this has worked, and a "\d TABLENAME" shows that the column is >> varchar(35). > >> But I still have messages in my log saying: >> ERROR: value too long for type character varying(20) > > Cached plans maybe? > > regards, tom lane > Could be. How should I circumvent them? The table is being vacuum analyzed every hour. Why are the plans cached?
On Feb 3, 2009, at 5:21 AM, Phoenix Kiula wrote: > On Mon, Jan 12, 2009 at 1:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > .....<snip>.... > >> 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 :-() >> > > > > Tom, this has worked, and a "\d TABLENAME" shows that the column is > varchar(35). > > But I still have messages in my log saying: > > --- > ERROR: value too long for type character varying(20) > --- > > Why is this? There are no other varchar(20) columns in my DB at all, > no other table. Only this column used to be 20 characters and using > your command I changed it to 35. It looks alright, the column also > accepts the value, but the error is logged. > > I'm confused. How to stop the error? You don't happen to have any functions that use varchar(20) in their arguments or for local variables? I'm not sure they'd cause the shown error, but I expect them to. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4987f368747033931215266!
On Tue, Feb 3, 2009 at 3:33 PM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > On Feb 3, 2009, at 5:21 AM, Phoenix Kiula wrote: > >> On Mon, Jan 12, 2009 at 1:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> .....<snip>.... >> >>> 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 :-() >>> >> >> >> >> Tom, this has worked, and a "\d TABLENAME" shows that the column is >> varchar(35). >> >> But I still have messages in my log saying: >> >> --- >> ERROR: value too long for type character varying(20) >> --- >> >> Why is this? There are no other varchar(20) columns in my DB at all, >> no other table. Only this column used to be 20 characters and using >> your command I changed it to 35. It looks alright, the column also >> accepts the value, but the error is logged. >> >> I'm confused. How to stop the error? > > > You don't happen to have any functions that use varchar(20) in their > arguments or for local variables? I'm not sure they'd cause the shown error, > but I expect them to. > Nope. We don't have any functions. I don't know what "local variables" mean. We don't use much PLSQL type stuff. Everything is done in PHP, so no, we don't have any varchar(20) appearing in PHP code.
On Mon, 2009-01-12 at 14:42 +0800, Phoenix Kiula wrote: > > 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: > > ...snip.... > > > Thanks Tom. Yes, no data change is required. But I don't think I have > the understanding to try what you suggested. Running the alter table > on my home database (almost same data) took an hour and it wasn't > complete. I had even dropped the index. So I guess altering it that > way is a pain in PGSQL. Bummer. > Actually, I did the same thing on a live database and it worked!