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

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

Post by Jan Wie » Thu, 28 Mar 2002 05:02:38

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


- Show quoted text -

> 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?


1. Altering a CHAR(4) column CHAR(5) changing

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


Programmer & System Administrator - Edistar srl

Il mar, 2002-03-26 alle 18:29, Tom Lane ha scritto:

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

2. FoxPro Databases FAQ #5: WHERE resource = onthenet

3. Searching Char(0) to Char(31) in NVARCHAR column

4. DB2_FORCE_FCM_BP registry value

5. Storing non-printable chars in CHAR columns

6. sp_makewebtask

7. Can't insert char into CHAR column

8. SQL Server 2000 Licence

9. Informix column change column type from char to int

10. change all char columns to varchar

11. change char columns to varchar

12. Error after change column from char(7) to varchar(10)

13. How to change column type NCHAR back to CHAR