On Wed, Mar 19, 2014 at 11:27 PM, Emi Lu <emilu@xxxxxxxxxxxxxxxxx> wrote: > Is there a way to change a table column from varchar(n) to varchar with > views depended on it. Nope. You cannot update the data type of a table column if it is used by a view. =# create table aa (a varchar(4)); CREATE TABLE =# create view aav as select * from aa; CREATE VIEW Time: 13.605 ms =# alter table aa alter column a set data type varchar; ERROR: 0A000: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view aav depends on column "a" LOCATION: ATExecAlterColumnType, tablecmds.c:7814 > The problem is that I do not want to drop and re-create all views > dependencies. I am looking for a way/command that can change the column type > and all views will be auto updated as well. A solution to minimize the impact on users would be to do all those operations inside the same transaction: begin; drop view v1; [...] alter table tab alter column col set data type mytype; create view v1 as select blabla; [...] commit; Regards, -- Michael -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
