Thread: Altering a CHAR(4) column CHAR(5) changing pg_attribute
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 anyone has any tip... The related row is: attrelid = 18865 attname = channel atttypid = 1042 attdispersion = 0.0022182 attlen = -1 attnum = 7 attnelems = 0 attcacheoff = -1 atttypmod = 8 attbyval = f attstorage = x attisset = f attalign = i attnotnull = t atthasdef = f Thank to everyone for your replies... -- Doct. Eng. Denis Gasparin: denis@edistar.com --------------------------- Programmer & System Administrator - Edistar srl
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)
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> 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).
But you could do
update foo set mycol = mycol || '';
after tweaking the atttypmod as Stephan illustrates. That should force
all the values to the correct length. (The dummy concatenation is just
to prevent the system from optimizing away the length coercion step.)
regards, tom lane
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@postgresql.org)
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
I have done my tests too and it worked... I think this is a thing to put in the FAQ or in some other place... This will help all persons who have to convert "textual" types between them without dropping and recreating tables, indexes, etc... Thank you for your help... -- Doct. Eng. Denis Gasparin: denis@edistar.com --------------------------- Programmer & System Administrator - Edistar srl Il mar, 2002-03-26 alle 18:29, Tom Lane ha scritto: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > 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). > > But you could do > update foo set mycol = mycol || ''; > after tweaking the atttypmod as Stephan illustrates. That should force > all the values to the correct length. (The dummy concatenation is just > to prevent the system from optimizing away the length coercion step.) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)