<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hello, <br>
<br>
On 08/29/2014 03:16 PM, Adrian Klaver wrote:<br>
</div>
<blockquote cite="mid:5400D1A8(dot)4050100(at)aklaver(dot)com" type="cite">
<blockquote type="cite">
<blockquote type="cite">May I know is there a way to "alter
column type to varchar" (previous
<br>
is varchar(***)) without view drop/re-creation?
<br>
<br>
Basically, looking for a way to change column without have to
<br>
drop/re-create dependent views.
<br>
<br>
varchar(***) to varchar and no date/numeric changes.
<br>
<br>
</blockquote>
I saw docs mention about: update pg_attribute. May I know:
<br>
<br>
. will dependent views updated automatically or there might be
potential
<br>
problems?
<br>
. If it's fine, will the following SQL enough to change column
from
<br>
varchar(***) to varchar?
<br>
<br>
update pg_attribute set atttypmod =-1
<br>
where attrelid = 'oid' ;
<br>
</blockquote>
<br>
Here is what I did. I would definitely test first and run in a
transaction:
<br>
</blockquote>
<br>
<br>
<blockquote cite="mid:5400D1A8(dot)4050100(at)aklaver(dot)com" type="cite">
<br>
test=# SELECT version();
<br>
version <br>
--------------------------------------------------------------------------------------------------------------------------
<br>
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]
<br>
<br>
<br>
test=# create TABLE base_tbl (id integer, vc_fld varchar(10));
<br>
CREATE TABLE
<br>
test=# CREATE view v_test as SELECT * from base_tbl ;
<br>
CREATE VIEW
<br>
test=# insert INTO base_tbl VALUES(1, 'one');
<br>
INSERT 0 1
<br>
test=# insert INTO base_tbl VALUES(2, 'two');
<br>
INSERT 0 1
<br>
test=# \d base_tbl
<br>
Table "public.base_tbl"
<br>
Column | Type | Modifiers
<br>
--------+-----------------------+-----------
<br>
id | integer |
<br>
vc_fld | character varying(10) |
<br>
<br>
test=# \d v_test
<br>
View "public.v_test"
<br>
Column | Type | Modifiers
<br>
--------+-----------------------+-----------
<br>
id | integer |
<br>
vc_fld | character varying(10) |
<br>
View definition:
<br>
SELECT base_tbl.id, base_tbl.vc_fld
<br>
FROM base_tbl;
<br>
<br>
test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
'base_tbl'::regclass AND attname = 'vc_fld';
<br>
UPDATE 1
<br>
test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
'v_test'::regclass AND attname = 'vc_fld';
<br>
UPDATE 1
<br>
test=# \d base_tbl
<br>
Table "public.base_tbl"
<br>
Column | Type | Modifiers
<br>
--------+-------------------+-----------
<br>
id | integer |
<br>
vc_fld | character varying |
<br>
<br>
test=# \d v_test
<br>
View "public.v_test"
<br>
Column | Type | Modifiers
<br>
--------+-------------------+-----------
<br>
id | integer |
<br>
vc_fld | character varying |
<br>
View definition:
<br>
SELECT base_tbl.id, base_tbl.vc_fld
<br>
FROM base_tbl;
<br>
<br>
test=# insert INTO base_tbl VALUES(3,
'123456789012345678901234567890');
<br>
INSERT 0 1
<br>
test=# SELECT * from base_tbl ;
<br>
id | vc_fld
<br>
----+--------------------------------
<br>
1 | one
<br>
2 | two
<br>
3 | 123456789012345678901234567890
<br>
(3 rows)
<br>
<br>
test=# SELECT * from v_test ;
<br>
id | vc_fld
<br>
----+--------------------------------
<br>
1 | one
<br>
2 | two
<br>
3 | 123456789012345678901234567890
<br>
(3 rows)
<br>
</blockquote>
<br>
<font color="#000099"><b>This is exactly what I plan to do</b></font>.
So, according to the test result, can make <font color="#660000">conclusion</font>
that pg_attribute will auto take care of all dependent views. <br>
<br>
>> Here is what I did. I would definitely test first and run
in a transaction:
<br>
<br>
It seems that there is no transaction block needed? The one line
command is: <br>
UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
'table_name'::regclass AND attname = 'col1';
<br>
Isn't it? <br>
<br>
As for the "definitely test", you mean check view after the change?
Would there be any other potential problems for this approach? <br>
<br>
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. <br>
<br>
Thanks a lot!<br>
Emi<br>
</body>
</html>