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:

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

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 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/

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 aliases for the exact same thing in the back end. Just the latter allows for that limit (n) to be specified if your specs require. Leaving that off saves a few validation cycles, but at the possible expense of schema and data incompatability with 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:

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

<snip>

Also, you could replace that pg_class sub-select with "'foo'::regclass".

Interesting....!!!

$ edb-psql.exe edb -c "select 'pg_class'::regclass;"
regclass
----------
pg_class
(1 row)

$ edb-psql.exe edb -c "select 'pg_class'::regclass::int;"
int4
------
1259
(1 row)

Really interesting....!!

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com