postgresql - changing column datatypes

[Anon]

how would i go about changing a column's datatype from say: timestamp to int(10)?

[chaostik]

Hi,

You cannot, as far as I am aware, change a column's datatype. This is because the database cannot necessarily be expected to know how to transform a columns data from one datatype to another. How for example could you tranform a string to an int?

The most you could hope to do is insert a new column and delete the old one. You can insert a new column into an existing table by:

ALTER TABLE mytable ADD newcolumn int(10);

However, PostgreSQL is not fully SQL92 compliant with this command though and does not implement a DROP clause. Therefore, to drop a column you have to actually recreate the database:

CREATE TABLE temp AS SELECT col1, newcolumn FROM mytable;
DROP TABLE;
CREATE TABLE mytable (
col1 int,
newcolumn int(10)
);
INSERT INTO mytable SELECT * FROM temp;
DROP TABLE temp;

Pretty nasty really. Yuck!

HTH. All the best,

Louis

[Anon]

blah, that's what i thought 😛

thanks a bunch!

[smarlowe]

A nice trick to use to delete columns is to rename the table then do a select into to recreate it the way you want it.

Fer instance:

create table test (f1 text, f2 text, f3 int);

(insert lots of data here)

to delete f2, do this:

select f1, f3 into new_table from test;

drop table test;
alter table new_table rename to test;

Note that this makes things like auto-incrementing fields break, and in that instance I've often used pg_dump to dump out a table or database and make the changes there.