Обсуждение: Modifying column size

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?

Thanks!
--
Susan Lane
DPN, Incorporated
4631 Spring Mountain Road
Las Vegas, NV 89102
Email  suel@dpn.com
Ph. (702) 873-3282
Fax (702) 873-3913
http://www.dpn.com

On Fri, Jul 19, 2002 at 02:14:10PM -0700, 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?

No can do.  You could work around this if you had no triggers,
foreign keys, &c. on those columns, by adding a new column of the
desired size and type, then updating it with the required data, then
renaming the old column, then renaming the new column to the desired
name.  (No, you can't drop the old column, either, but you could set
it to all null to save space.)

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

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.

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?

In general, not really (as per other answers).

For the specific cases of varchar of one size to varchar of a larger size,
you can do so with some system table hacking.  In general, unless you've
got constraints/views/etc to prevent you from making a new table, copying
the data and renaming, I'd suggest against it.  (If you really want to
know, you can ask about it specifically or better yet, check the archives
:) )

"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:20020719150925.D85491-100000@megazone23.bigpanda.com...
> In general, not really (as per other answers).
>
> For the specific cases of varchar of one size to varchar of a larger size,
> you can do so with some system table hacking.  In general, unless you've
> got constraints/views/etc to prevent you from making a new table, copying
> the data and renaming, I'd suggest against it.  (If you really want to
> know, you can ask about it specifically or better yet, check the archives
> :) )

Thanks to everyone for the quick responses!

What I ended up doing is dumping the data, dropping the table (which created
problems, since I had constraints), recreating the table with my changed
varchar size, and restoring the data.  I think the suggestion to create a
new table and copying it over is a better solution.....I will still have to
deal with my constraints, but at least there are less steps!  :)