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

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).

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)