Thread: Changing column question..

I just want to make sure that from what I understand I can not change a column type from varchar(20) to text or
anythingelse without dropping the table.  From my reading would the easies thing to do be to create a temp table
exactlyidentical to my current table.. copy all of the information over from one table to another.. (is there an easy
SQLcommand to do this.. I've done from one column in table a to one column in table b.. but never the whole table).
Dropmy old table.. recreate it in the new format.. then copy all of my information back? 

Also,
    Where can you decide/change postgresql's behavior on dealing with trying to put too much information in a column
witha type of varchar(20).. I want it to insert everything up to 20 chars then drop the rest.. currently it dies with
anerror about trying to put in to much information.. 

Travis

On Thu, 2002-12-19 at 16:07, Williams, Travis L, NPONS wrote:
> I just want to make sure that from what I understand I can not change
> a column type from varchar(20) to text or anything else without
> dropping the table.

No, you don't need to drop the table. You can add a new column of the
type you want to change the existing column two, then move the data from
the old data to the new column (using UPDATE), then drop the old column,
and rename the new column to the name of the old column. This works in
7.3+ (as that's the first version that allowed you to drop columns).

> I want it to insert everything up to 20 chars then drop the rest..

Then call substring() on the value you're inserting to chop it down to
20 chars or less.

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Neil Conway <neilc@samurai.com> writes:
> On Thu, 2002-12-19 at 16:07, Williams, Travis L, NPONS wrote:
>> I want it to insert everything up to 20 chars then drop the rest..

> Then call substring() on the value you're inserting to chop it down to
> 20 chars or less.

Or do an explicit cast.  'foobar'::varchar(3) produces 'foo' (in 7.3
anyway), whereas assigning 'foobar' directly to a varchar(3) column
produces an error.  Yeah, it's a little weird, but by my reading of
the SQL spec, that's the spec-mandated behavior.

            regards, tom lane