Re: Altering a CHAR(4) column CHAR(5) changing pg_attribute

Stephan Szabo wrote:
>
> On 26 Mar 2002, Denis Gasparin wrote:
>
> > I have a table with a column of type CHAR(4) and I want to change the
> > column type to CHAR(5). This table is referenced by many other tables
> > and dropping it and recreating will be a massacre...
> >
> > So I have had this idea:
> > why do not change the row of that column in the pg_attribute system
> > table?
> >
> > In particular my idea is to change the atttypmod from 8 to 9 (I have
> > thought char(5) is larger 1 byte than char(4)...then...).
> >
> > Is this possible? There will be bad consequences for my table?
>
> If you were using varchar, this would be fine. With char, you have
> issues with the padding spaces if you ever convert them to text
> (for example using lower or upper).

The padding issue with char is, that it is actually padded on
input, and the change in the atttypmod doesn't change the
padding of the individual values. To correct that, you'd
have to touch all the existing values, so they go through the
padding again.

UPDATE q1 SET a = a || '';

would do the job just fine in your example.

Jan

>
> In my test:
> create table q1(a char(4));
>
> insert into q1 values ('a');
>
> update pg_attribute set atttypmod=9 where attrelid=(Select
> oid from pg_class where relname='q1') and attname='a';
>
> insert into q1 values ('a');
>
> select * from q1, q1 q2 where q1.a=q2.a;
> a | a
> -------+-------
> a | a
> a | a
> a | a
> a | a
> (4 rows)
>
> select * from q1, q1 q2 where lower(q1.a)=lower(q2.a);
> a | a
> -------+-------
> a | a
> a | a
> (2 rows)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com