i have created a table
userlist with fields username varchar(10)
now i want to change it to username varchar(30)
without dropping or recreating the table.
how do i do it?
tina







i have created a table
userlist with fields username 
varchar(10)
now i want to change it to username 
varchar(30)
without dropping or recreating the 
table.
how do i do it?
tina
Wed, 5 Dec 2001 18:42:07 +0530
"tinar" <tinarajraj@hotmail.com>
how to chane  the type

On Wed, 5 Dec 2001, tinar wrote:

> i have created a table
> userlist with fields username varchar(10)
> now i want to change it to username varchar(30)
> without dropping or recreating the table.
> how do i do it?

The best way is to recreate the table and rename
them around.  If you *REALLY* don't want to do
that and have a recent backup (yes, I'm serious),
you can muck with pg_attribute and change
atttypmod for the attribute in question
(from 14 to 34).



Thu, 6 Dec 2001 08:31:09 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: how to chane  the type
What's the essential problem with changing column types in postgres?  Is it
similar to the DROP COLUMN problem?

If the answer is that the table format only has allocated enough space per
row for the existing type, then how is it possible that Stephen's hack below
will not break things?

Chris

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo
> Sent: Friday, 7 December 2001 12:31 AM
> To: tinar
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] how to chane the type
>
>
>
> On Wed, 5 Dec 2001, tinar wrote:
>
> > i have created a table
> > userlist with fields username varchar(10)
> > now i want to change it to username varchar(30)
> > without dropping or recreating the table.
> > how do i do it?
>
> The best way is to recreate the table and rename
> them around.  If you *REALLY* don't want to do
> that and have a recent backup (yes, I'm serious),
> you can muck with pg_attribute and change
> atttypmod for the attribute in question
> (from 14 to 34).
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


Fri, 7 Dec 2001 09:45:35 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: how to chane  the type

On Fri, 7 Dec 2001, Christopher Kings-Lynne wrote:

> What's the essential problem with changing column types in postgres?  Is it
> similar to the DROP COLUMN problem?
>
> If the answer is that the table format only has allocated enough space per
> row for the existing type, then how is it possible that Stephen's hack below
> will not break things?

The hack below only works to change the max length of variable length
attributes and only upward.  I'd be very wary of trying to change the real
type of a value except between ones that are bitwise compatible (like I
think varchar and text are technically, but I'm not sure).

> > The best way is to recreate the table and rename
> > them around.  If you *REALLY* don't want to do
> > that and have a recent backup (yes, I'm serious),
> > you can muck with pg_attribute and change
> > atttypmod for the attribute in question
> > (from 14 to 34).


Thu, 6 Dec 2001 18:05:14 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: how to chane  the type
OK, I'm kind of interested now in how the variable length attributes are
actually stored on disk, that you are able to increase them, but not
decrease?

I would have thought the other way around?

Chris

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Friday, 7 December 2001 10:05 AM
> To: Christopher Kings-Lynne
> Cc: tinar; pgsql-sql@postgresql.org
> Subject: RE: [SQL] how to chane the type
>
>
>
> On Fri, 7 Dec 2001, Christopher Kings-Lynne wrote:
>
> > What's the essential problem with changing column types in
> postgres?  Is it
> > similar to the DROP COLUMN problem?
> >
> > If the answer is that the table format only has allocated
> enough space per
> > row for the existing type, then how is it possible that
> Stephen's hack below
> > will not break things?
>
> The hack below only works to change the max length of variable length
> attributes and only upward.  I'd be very wary of trying to change the real
> type of a value except between ones that are bitwise compatible (like I
> think varchar and text are technically, but I'm not sure).
>
> > > The best way is to recreate the table and rename
> > > them around.  If you *REALLY* don't want to do
> > > that and have a recent backup (yes, I'm serious),
> > > you can muck with pg_attribute and change
> > > atttypmod for the attribute in question
> > > (from 14 to 34).
>


Fri, 7 Dec 2001 10:09:59 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] how to change  the type

On Fri, 7 Dec 2001, Christopher Kings-Lynne wrote:

> OK, I'm kind of interested now in how the variable length attributes are
> actually stored on disk, that you are able to increase them, but not
> decrease?
>
> I would have thought the other way around?

IIRC, the values are stored as length + data.  I think char() might
do wierd things (I don't know if the trailing spaces are stored), but
varchar() and text should be expandable because anything that could have
fit before should still fit and look the same.  Going down is
problematic, because if you have a varchar(5) field where one value is say
'abcd' and you make it varchar(3) what happens?



Thu, 6 Dec 2001 18:41:09 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: [SQL] how to change  the type
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> IIRC, the values are stored as length + data.  I think char() might
> do wierd things (I don't know if the trailing spaces are stored), but
> varchar() and text should be expandable because anything that could have
> fit before should still fit and look the same.

Yup, exactly.

> Going down is
> problematic, because if you have a varchar(5) field where one value is say
> 'abcd' and you make it varchar(3) what happens?

What would actually happen right now is nothing: the value would still
be 'abcd' and would still read out that way.  The 3-char limit would
only get enforced during inserts and updates of the column.

char(N) does store the trailing spaces, so altering N would give
unwanted results: again, existing values would read out with the old
width until updated.  You could fix this by issuing

	UPDATE tab SET col = col

after tweaking the pg_attribute.atttypmod value.  (AFAICS, any "clean"
implementation would have to do just that internally, with the same
unpleasant space and speed implications as we've discussed for DROP
COLUMN.)

			regards, tom lane

Fri, 07 Dec 2001 10:36:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] how to change the type 
On Fri, 7 Dec 2001, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Going down is
> > problematic, because if you have a varchar(5) field where one value is say
> > 'abcd' and you make it varchar(3) what happens?
>
> What would actually happen right now is nothing: the value would still
> be 'abcd' and would still read out that way.  The 3-char limit would
> only get enforced during inserts and updates of the column.

That's what I figured, but I also assume that'd be "wrong" in a pure sense
since the value is invalid for the new datatype, so I figure its safer
to say up only. :)


Fri, 7 Dec 2001 13:41:46 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: [SQL] how to change the type 
> char(N) does store the trailing spaces, so altering N would give
> unwanted results: again, existing values would read out with the old
> width until updated.  You could fix this by issuing
>
> 	UPDATE tab SET col = col
>
> after tweaking the pg_attribute.atttypmod value.  (AFAICS, any "clean"
> implementation would have to do just that internally, with the same
> unpleasant space and speed implications as we've discussed for DROP
> COLUMN.)

Can I take this opportunity to give my little thought on operations like
these (alter column type, drop column, etc.?)

If the DBA had to issue these commands every 5 minutes, then the speed and
space implications would be bad, yeah.  However, if all I want to do is drop
a column once every 6 months, then I don't really care that the operation
might take a minute and might consume lots of disk space...


Chris


Mon, 10 Dec 2001 10:32:56 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] how to change the type 