"ALTER COLUMN ... TYPE" problem

The-Saint · #1 (**permalink**) 10-28-04, 17:30

table user_general has a field named user_name which is varchar(40) now. I want to make it varchar(15). Here is SQL I use:

ALTER TABLE user_general
ALTER COLUMN user_name TYPE varchar(15)

it gives me error:

SQL error:
ERROR: syntax error at or near "TYPE" at character 50
In statement:
ALTER TABLE user_general
ALTER COLUMN user_name TYPE varchar(15)

zalew · #2 (**permalink**) 10-28-04, 22:06

hi. in postgres you can't so easily change column type.. the documentation of 'alter table' there's no word about changing column type, as if they didn't want to talk about it

for a long time i thought it was only possible by recreating the table, but i found something like this in the faq

Quote:

To change the data type of a column, do this:

BEGIN;
ALTER TABLE tab ADD COLUMN new_col new_data_type;
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
ALTER TABLE tab DROP COLUMN old_col;
COMMIT;

You might then want to do VACUUM FULL tab to reclaim the disk space used by the expired rows.

danie · #3 (**permalink**) 11-25-04, 01:27

Quote:

Originally Posted by zalew

hi. in postgres you can't so easily change column type.. the documentation of 'alter table' there's no word about changing column type, as if they didn't want to talk about it

for a long time i thought it was only possible by recreating the table, but i found something like this in the faq

This works well, but remeber to add a line to rename new_col to old_col.
The new col now renamed to old_col will appear at the end of the list of fields in the table definition.