alter table alter column to resize a varchar
alter table alter column to resize a varchar
От:
Gary Stainburn <gary.stainburn@ringways.co.uk>
Дата:
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
Re: alter table alter column to resize a varchar
От:
Craig James <cjames@emolecules.com>
Дата:
On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn 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
Re: alter table alter column to resize a varchar
От:
Gary Stainburn <gary.stainburn@ringways.co.uk>
Дата:
On Thursday 27 September 2012 15:49:53 Craig James wrote: > On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn > > 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. > I had an arbitary limit of 80 to keep the data managable within the applications. Now that limit is insufficient it may be worth re-evaluating the requirement. > > 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. I have similar scripts for most of my scripts, but wasn't sure if I'd have to use them. Looks like I will, so I'll have to check that they're up-to-date. Thanks for the help Craig -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk