Re: alter table alter column to resize a varchar

On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn
<gary(dot)stainburn(at)ringways(dot)co(dot)uk> wrote:
> Hi folks.
>
> I'm planning on extending a field in one of my main tables using:
>
> 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.

> 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