Re: Modifying column size

On Fri, 19 Jul 2002, Susan Lane wrote:

> I was wondering if anyone here knew of a way to modify the size of a column
> in Postgres.  In Oracle, you can use the following syntax:
>
>     alter table usrs modify passwd varchar2(32);    (For Postgres, I changed
> the datatype to varchar)
>
> But, when I try this in psql, it doesn't like the modify keyword.  I looked
> in the documentation, and I found a doc note that someone else had tried
> this as well to no avail.  Anybody know what is the correct syntax or if
> this can even be done in Postgres?

Sorry, but right now postgresql doesn't support type changes or column
dropping.  There are many ways to work around this, the most common being
selecting the fields you want to keep into a new table, coercing them to
the new types as you go, then dropping the old table and renaming the new
one.  Like so:

create table test (name text, address varchar(16), id int);
(insert your data and mix well for a few weeks)

select name, address::text, id into newtable;
drop table test;
alter table newtable rename to test;

The above example changes the poorly chosen varchar(16) field known as
test into a text field.

Note that text fields are the preferred method for storing char
information in postgresql, unless you specifically need to limit field
size, and even then, it might be better to use a constraint than a data
type to do it.