Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Question Unanswered: "ALTER COLUMN ... TYPE" problem

    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)

  2. #2
    Join Date
    Oct 2004
    Posts
    3
    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.

  3. #3
    Join Date
    Nov 2004
    Posts
    1

    changing column type

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •