Thread: How can I change type of column

Can I change type of column with alter table command?

For instance if I have a table t1 and column c1 which is defined as
decimal(18, 2) and I want to change it into decimal(18, 3) in MS SQL
server I can write:

alter table t1 alter column c1 decimal(18, 3)

Is there something similar in PostgreSQL? Tnx in advance.

Dragan

On Wed, Mar 31, 2004 at 17:36:12 +0200,
  mlists <mlists@panforma.co.yu> wrote:
> Can I change type of column with alter table command?
>
> For instance if I have a table t1 and column c1 which is defined as
> decimal(18, 2) and I want to change it into decimal(18, 3) in MS SQL
> server I can write:
>
> alter table t1 alter column c1 decimal(18, 3)
>
> Is there something similar in PostgreSQL? Tnx in advance.

Not yet.
You can do this in several commands in a single transaction in recent versions
of postgres. You can do an add, update, drop and rename to affect the same
change.
In this case you might be able to do the change by updating the catalog
tables directly, but I don't know exactly what you would need to do.
Unless your table is huge it probably isn't worth doing it that way.

On 2004.03.31 09:36 mlists wrote:
> Can I change type of column with alter table command?
>
> For instance if I have a table t1 and column c1 which is defined as
> decimal(18, 2) and I want to change it into decimal(18, 3) in MS SQL
> server I can write:
>
> alter table t1 alter column c1 decimal(18, 3)
>
> Is there something similar in PostgreSQL? Tnx in advance.

Not that I know of.  You either have to add a new column,
copy the data with UPDATE, drop the old column, and then alter the new
column to disallow NULL if necessary or you have to
do a pg_dump on the table and drop and re-create the table.
The latter if you've scripts or whatever relying on the
'order' of the columns as returned with SELECT * type
statements.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein