PDA

View Full Version : "ALTER COLUMN ... TYPE" problem

The-Saint
10-28-04, 18: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
10-28-04, 23: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

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
11-25-04, 02:27
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.