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

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

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/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFsNMYIB7bNG8LQkwRAgWAAJ97k2UEXXIj0VU7ks/zjB5X6+vcAgCdEMw0
P2e6xPao6r+d66sC8shODQA=
=om5c
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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

On 1/20/07, Kelly Burkhart <kelly.burkhart@gmail.com> wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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)

On 1/20/07, Kelly Burkhart <kelly.burkhart@gmail.com> 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)?
>
> 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
>

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match