pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
alter table alter column to resize a varchar 3+ messages / 2 participants [nested] [flat]
* alter table alter column to resize a varchar @ 2012-09-27 09:23 Gary Stainburn <gary.stainburn@ringways.co.uk> 0 siblings, 1 reply; 3+ messages in thread From: Gary Stainburn @ 2012-09-27 09:23 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: alter table alter column to resize a varchar @ 2012-09-27 14:49 Craig James <cjames@emolecules.com> parent: Gary Stainburn <gary.stainburn@ringways.co.uk> 0 siblings, 1 reply; 3+ messages in thread From: Craig James @ 2012-09-27 14:49 UTC (permalink / raw) To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-admin On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn <gary.stainburn@ringways.co.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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: alter table alter column to resize a varchar @ 2012-09-27 15:09 Gary Stainburn <gary.stainburn@ringways.co.uk> parent: Craig James <cjames@emolecules.com> 0 siblings, 0 replies; 3+ messages in thread From: Gary Stainburn @ 2012-09-27 15:09 UTC (permalink / raw) To: pgsql-admin; +Cc: Craig James <cjames@emolecules.com> On Thursday 27 September 2012 15:49:53 Craig James wrote: > On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn > > <gary.stainburn@ringways.co.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. > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-09-27 15:09 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-09-27 09:23 alter table alter column to resize a varchar Gary Stainburn <gary.stainburn@ringways.co.uk> 2012-09-27 14:49 ` Craig James <cjames@emolecules.com> 2012-09-27 15:09 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox