Thread: Re: Alter definition of a column

Hello,

The +4 is for the overhead of a varchar field.

Using ALTER TABLE <table> ALTER <field> TYPE VARCHAR(<newsize>) to
change the size requires scanning the entire table.  For large tables,
this will be much slower than the pg_attribute query.  Both will get
the job done.

af300wsm@gmail.com wrote:
> Hello,
>
> In this case, I don't have to alter the definition to the extent of
> changing the data type, but rather to change the length of a varchar
> field/column.  I found the following from a past posting to one of the
> PostgreSQL user lists that does work, but I'd like something a bit more
> elegant and wanted to find out if anything better exists in PostgreSQL
> 8.1.3 before I went and altered more than my test DB.  Here is what I
> found (note that the person he was responding to wanted to bring his
> varchar field from 10 to 75 characters, in light of this, why is 4
> added to 75?):
>
>     update pg_attribute set atttypmod = 75 + 4
>     where attname = 'columnname' and
>     attrelid = (select oid from pg_class where relname = 'tablename');
>
> The above comes from a posting made in Oct. of 2001.  Also, I found
> this posting to this newsgroup in Oct. of last year mentioning:
>
> <quote>
> > Am I right in thinking that altering a column from varchar(n) to
> > varchar(n+m) requires each tuple to be visited?
>
> Yes.  Doing otherwise would require an unreasonable amount of
> data-type-specific knowledge hardwired into ALTER COLUMN TYPE.
> </quote>
>
> What is this talking about and how does it apply to what I need to do?
>
> Thanks,
> Andy

ksherlock@gmail.com wrote:
> Hello,
>
> The +4 is for the overhead of a varchar field.
>
> Using ALTER TABLE <table> ALTER <field> TYPE VARCHAR(<newsize>) to
> change the size requires scanning the entire table.  For large tables,
> this will be much slower than the pg_attribute query.  Both will get
> the job done.
>

Thanks.  Am I correct in assuming that this scanning of the entire
table is done when I use the 'ALTER TABLE' command and not something I
must do after it's done?

Thanks again for explaining it.  I was thinking that the +4 had
something to do with overhead of some sort, but wanted to know for
sure.

Andy

On Thu, Jan 18, 2007 at 06:27:04AM -0800, af300wsm@gmail.com wrote:
> ksherlock@gmail.com wrote:
> > Using ALTER TABLE <table> ALTER <field> TYPE VARCHAR(<newsize>) to
> > change the size requires scanning the entire table.  For large tables,
> > this will be much slower than the pg_attribute query.  Both will get
> > the job done.
>
> Thanks.  Am I correct in assuming that this scanning of the entire
> table is done when I use the 'ALTER TABLE' command and not something I
> must do after it's done?

ALTER TABLE, to be correct, actually has to check the entire table to
make sure it's ok. By doing it directly you're basically telling the DB
it's OK.

For making a varchar column longer it's safe though, and the easiest way.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

af300wsm@gmail.com wrote:

> Thanks.  Am I correct in assuming that this scanning of the entire
> table is done when I use the 'ALTER TABLE' command and not something I
> must do after it's done?

Yes, ALTAR TABLE ... scans through the entire table when it does the
update, it's not something you need to do.

On 1/19/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
>
> ALTER TABLE, to be correct, actually has to check the entire table to
> make sure it's ok. By doing it directly you're basically telling the DB
> it's OK.
>
> For making a varchar column longer it's safe though, and the easiest way.

Is it possible to use a similar method to change a varchar(n) to text
with no length constraint?

-K

Should help --> ALTER TABLE tablename ALTER columname TYPE text;

----------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 1/20/07, Shoaib Mir <shoaibmir@gmail.com> wrote:
> Should help --> ALTER TABLE tablename ALTER columname TYPE text;

I was looking for a way to alter a column from varchar(n) to text
without using the alter command and consequently touching every single
row.  Below is sql which seems to work, but is it advisable to do such
shenanigans?  (are varchar and text the same thing)?

kelly=# create table foo( c1 varchar(4) not null, c2 text not null );
CREATE TABLE
kelly=#
kelly=# update pg_attribute set atttypid=25, atttypmod=-1
kelly-# where attname = 'c1' and attrelid =
kelly-#     (select oid from pg_class where relname = 'foo');
UPDATE 1
kelly=#
kelly=# \d foo
    Table "public.foo"
 Column | Type | Modifiers
--------+------+-----------
 c1     | text | not null
 c2     | text | not null

I haven't used it this way, anyone else who did might be able to comment on it. Why will you not like to use the ALTER table command? I think a text should be use in case you don't know the limit for characters (much faster too in that case I guess) in a column but if you know the limits then you should be using varchar(n).

-----------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

From: "Kelly Burkhart" <kelly.burkhart@gmail.com>
> On 1/20/07, Shoaib Mir <shoaibmir@gmail.com> wrote:
> > Should help --> ALTER TABLE tablename ALTER columname TYPE text;
>
> I was looking for a way to alter a column from varchar(n) to text
> without using the alter command and consequently touching every single
> row.  Below is sql which seems to work, but is it advisable to do such
> shenanigans?  (are varchar and text the same thing)?

Always have been under the impression myself that text and varchar (and character varying for that matter) were all
aliasesfor the exact same thing in the back end.  Just the latter allows for that limit (n) to be specified if your
specsrequire.  Leaving that off saves a few validation cycles, but at the possible expense of schema and data
incompatabilitywith other systems should that ever matter. 

So yeah, I don't see any reason it shouldn't work just fine.

 - Josh Williams

On Sat, Jan 20, 2007 at 11:19:50AM -0600, Kelly Burkhart wrote:
> On 1/20/07, Shoaib Mir <shoaibmir@gmail.com> wrote:
> >Should help --> ALTER TABLE tablename ALTER columname TYPE text;
>
> I was looking for a way to alter a column from varchar(n) to text
> without using the alter command and consequently touching every single
> row.  Below is sql which seems to work, but is it advisable to do such
> shenanigans?  (are varchar and text the same thing)?

text == varchar, which is varchar(x) without a limit. But the storage in
the table is the same in all 3 cases (in fact, the storage for char is
also the same).

> kelly=# create table foo( c1 varchar(4) not null, c2 text not null );
> CREATE TABLE
> kelly=#
> kelly=# update pg_attribute set atttypid=25, atttypmod=-1
> kelly-# where attname = 'c1' and attrelid =
> kelly-#     (select oid from pg_class where relname = 'foo');
> UPDATE 1
> kelly=#
> kelly=# \d foo
>    Table "public.foo"
> Column | Type | Modifiers
> --------+------+-----------
> c1     | text | not null
> c2     | text | not null

I know there's some considerations when altering system tables like
this; the archives probably have more details. You might be a bit safer
doing that in a database with no other connections. But in the case of
increasing a size limit (or removing one), ALTER shouldn't have to
re-read the entire table. AFAIK the only reason it does so right now is
it doesn't have the brains to know what cases it doesn't need to do this
on.

Also, you could replace that pg_class sub-select with "'foo'::regclass".
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On 1/22/07, Jim C. Nasby <jim@nasby.net> wrote:
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com