From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-admin@postgresql.org
Cc: Craig James <cjames@emolecules.com>
Subject: Re: alter table alter column to resize a varchar
Date: Thu, 27 Sep 2012 16:09:22 +0100
Message-ID: <201209271609.22833.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <CAFwQ8rdYdSRv==z2iXqbTdSKq0U0jogM5dqqC12ss-LAVutd=Q@mail.gmail.com>
References: <201209271023.57710.gary.stainburn@ringways.co.uk>
<CAFwQ8rdYdSRv==z2iXqbTdSKq0U0jogM5dqqC12ss-LAVutd=Q@mail.gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gary.stainburn@ringways.co.uk, cjames@emolecules.com
Subject: Re: alter table alter column to resize a varchar
In-Reply-To: <201209271609.22833.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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