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

On 08/29/2014 12:09 PM, Emi Lu wrote:
>
>> Hello list,
>>
>> 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)


>
> Thanks a lot!
>
>> ---
>> *PostgreSQL 8.3.18 on x86_64*
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com