alter table alter column to resize a varchar

[Gary Stainburn]

Hi folks.

I'm planning on extending a field in one of my main tables using:

alter table stock alter column type varchar(255);

to extend it from the current varchar(80). My concern is that this table and
this field is used in a number of views, and views of views.

Are then any gotchas that I need to worry about or things I need to check, or
will it just work?

Gary
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk

[Craig James]

On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn

Post by Gary Stainburn
Hi folks.
alter table stock alter column type varchar(255);

Why not just do

alter table stock alter column type text;

That is, do you really need a limit? The VARCHAR type in Postgress is
just a TEXT type with an additional test to limit data to 255
characters. It doesn't save space to limit the length.

Post by Gary Stainburn
to extend it from the current varchar(80). My concern is that this table and
this field is used in a number of views, and views of views.
Are then any gotchas that I need to worry about or things I need to check, or
will it just work?

You can't alter a table that has dependent views.

=> alter table foo alter t type varchar(255);
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view fooview depends on column "t"

I keep scripts around to rebuild all of my views when necessary. If I
keep the scripts up to date, it make me much more willing to make a
schema change instead of putting it off until things are really messy.

Craig