Re: alter column to varchar without view drop/re-creation - Mailing list pgsql-general

<meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">


    Hello,

      On 08/29/2014 03:16 PM, Adrian Klaver wrote:



        May I know is there a way to "alter
          column type to varchar" (previous

          is varchar(***)) without view drop/re-creation?


          Basically, looking for a way to change column without have to

          drop/re-create dependent views.


          varchar(***) to varchar and no date/numeric changes.



        I saw docs mention about: update pg_attribute. May I know:


        . will dependent views updated automatically or there might be
        potential

        problems?

        . If it's fine, will the following SQL enough to change column
        from

        varchar(***) to varchar?


           update pg_attribute set atttypmod =-1

           where  attrelid = 'oid' ;



      Here is what I did. I would definitely test first and run in a
      transaction:






      test=# SELECT version();


                                                        
version 

--------------------------------------------------------------------------------------------------------------------------

       PostgreSQL 8.3.23 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE
      Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012]



      test=# create TABLE base_tbl (id integer, vc_fld varchar(10));

      CREATE TABLE

      test=# CREATE view v_test as SELECT * from base_tbl ;

      CREATE VIEW

      test=# insert INTO base_tbl VALUES(1, 'one');

      INSERT 0 1

      test=# insert INTO base_tbl VALUES(2, 'two');

      INSERT 0 1

      test=# \d base_tbl

                Table "public.base_tbl"

       Column |        
Type         | Modifiers 

      --------+-----------------------+-----------

       id     |
integer              | 

       vc_fld | character varying(10) |


      test=# \d v_test

                  View "public.v_test"

       Column |        
Type         | Modifiers 

      --------+-----------------------+-----------

       id     |
integer              | 

       vc_fld | character varying(10) |

      View definition:

       SELECT base_tbl.id, base_tbl.vc_fld

         FROM base_tbl;


      test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
      'base_tbl'::regclass AND attname = 'vc_fld';

      UPDATE 1

      test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
      'v_test'::regclass AND attname = 'vc_fld';

      UPDATE 1

      test=# \d base_tbl

              Table "public.base_tbl"

       Column |       Type        | Modifiers

      --------+-------------------+-----------

       id     | integer           |

       vc_fld | character varying |


      test=# \d v_test

                View "public.v_test"

       Column |       Type        | Modifiers

      --------+-------------------+-----------

       id     | integer           |

       vc_fld | character varying |

      View definition:

       SELECT base_tbl.id, base_tbl.vc_fld

         FROM base_tbl;


      test=# insert INTO base_tbl VALUES(3,
      '123456789012345678901234567890');

      INSERT 0 1

      test=# SELECT * from base_tbl ;

       id |             vc_fld

      ----+--------------------------------

        1 | one

        2 | two

        3 | 123456789012345678901234567890

      (3 rows)


      test=# SELECT * from v_test ;

       id |             vc_fld

      ----+--------------------------------

        1 | one

        2 | two

        3 | 123456789012345678901234567890

      (3 rows)



    This is exactly what I plan to do.
    So, according to the test result, can make conclusion
    that pg_attribute will auto take care of all dependent views.

    >> Here is what I did. I would definitely test first and run
    in a transaction:


    It seems that there is no transaction block needed? The one line
    command is:
    UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
    'table_name'::regclass AND attname = 'col1';

    Isn't it?

    As for the "definitely test", you mean check view after the change?
    Would there be any other potential problems for this approach?

    If not, I will adopt this approach since we have many view
    dependencies and it seems that this was the best way to avoid view
    drop/re-creation for now. If there are other ways, please do let me
    know.

    Thanks a lot!
    Emi