Search Postgresql Archives

Re: alter column to varchar without view drop/re-creation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux