Обсуждение: Making a varchar bigger

Hello Group,

I've run into a bit of a problem, I have a varchar(6) field that I now need to
have as a varchar(12).

I am relatively new to postgres and am unsure how best to do this. What I
would like to do is alter the meta-data directly. Change the value of
atttypmod in pg_attribute directly. Initial tests look good, however I am
unsure how safe this is. Or what else needs to be done? does it need to be
re-indexed?

I have looked at the sections on renaming/changing columns, though this is
more a solid an option, I am dealing with millions of records and the
downtime for the backfill is not acceptable.

I found some old post in here hinting at doing this, however the links in them
are now dead.

--
Givex - http://www.givex.com/
Derik Barclay <dbarclay@givex.com>, Systems Software Engineer
+1 416 350 9660
+1 416 250 9661 (fax)

Specificaly I am looking at executing something like this:

UPDATE pg_attribute SET atttypmod = 16 where attname = 'x' AND attrelid =
(select pg_class.oid from pg_class where relname = 'mytable');

On December 23, 2004 05:13 pm, Derik Barclay wrote:
> Hello Group,
>
> I've run into a bit of a problem, I have a varchar(6) field that I now need
> to have as a varchar(12).
>
> I am relatively new to postgres and am unsure how best to do this. What I
> would like to do is alter the meta-data directly. Change the value of
> atttypmod in pg_attribute directly. Initial tests look good, however I am
> unsure how safe this is. Or what else needs to be done? does it need to be
> re-indexed?
>
> I have looked at the sections on renaming/changing columns, though this is
> more a solid an option, I am dealing with millions of records and the
> downtime for the backfill is not acceptable.
>
> I found some old post in here hinting at doing this, however the links in
> them are now dead.

--
Givex - http://www.givex.com/
Derik Barclay <dbarclay@givex.com>, Systems Software Engineer
+1 416 350 9660
+1 416 250 9661 (fax)

Derik Barclay <dbarclay@givex.com> writes:
> Specificaly I am looking at executing something like this:
> UPDATE pg_attribute SET atttypmod = 16 where attname = 'x' AND attrelid =
> (select pg_class.oid from pg_class where relname = 'mytable');

If you have any indexes or views referencing this column, then it's a
bit harder.  But for the table itself I believe that will work.

            regards, tom lane

It is combined with another field to form an index.
Does that mean a re-indexing will be required? or are there other issues?

On December 30, 2004 11:03 pm, Tom Lane wrote:
> Derik Barclay <dbarclay@givex.com> writes:
> > Specificaly I am looking at executing something like this:
> > UPDATE pg_attribute SET atttypmod = 16 where attname = 'x' AND attrelid =
> > (select pg_class.oid from pg_class where relname = 'mytable');
>
> If you have any indexes or views referencing this column, then it's a
> bit harder.  But for the table itself I believe that will work.
>
>    regards, tom lane

--
Givex - http://www.givex.com/
Derik Barclay <dbarclay@givex.com>, Systems Software Engineer
+1 416 350 9660
+1 416 250 9661 (fax)

Derik Barclay <dbarclay@givex.com> writes:
> On December 30, 2004 11:03 pm, Tom Lane wrote:
>> If you have any indexes or views referencing this column, then it's a
>> bit harder.  But for the table itself I believe that will work.

> It is combined with another field to form an index.
> Does that mean a re-indexing will be required? or are there other issues?

No, I'm just thinking you should update atttypmod for the index column.

            regards, tom lane