On 08/29/2014 02:29 PM, Emi Lu wrote:
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.
No you can not make that conclusion. I had to manually change the
atttypmod in the view.
>> 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?
There is more than one line. One for the base table and one for each
view that uses the base table.
As for the "definitely test", you mean check view after the change?
Would there be any other potential problems for this approach?
Well you are using a backdoor hack to directly alter a system table, so
yes there is a potential for problems. I would imagine in this case,
same base type just changing the length argument, the chances of
problems are slight. Still I would run some test queries against both
the base table and view(s) just to be sure.
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.
The only other way I know to do this is to:
BEGIN;
DROP VIEW some_view ;
ALTER TABLE some_table ALTER COLUMN some_col TYPE new_type;
CREATE OR REPLACE VIEW some_view SELECT * FROM some_table;
COMMIT;
Then everything is wrapped in a transaction and 'hidden' from other
sessions until complete.
Thanks a lot!
Emi
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
