by
> > 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).
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
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
_________________________________________________________
Do You Yahoo!?
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org