Altering columns in a table

Adding or changing columns to a table with the ALTER TABLE command.

The ALTER TABLE command can be used to add new columns to a table and to alter the column type of an existing column.

Procedure

  • Add a age column of type int to the table cycling.cyclist_alt_stats.
    ALTER TABLE cycling.cyclist_alt_stats ADD age int;

    This creates the column metadata and adds the column to the table schema, and sets the value to NULL for all rows.

    Verify that the column was added with null values.
    SELECT id, age FROM cycling.cyclist_alt_stats LIMIT 3;
    The result set shows the first three rows.
     id                                   | age
    --------------------------------------+------
     e0953617-07eb-4c82-8f91-3b2757981625 | null
     a9e96714-2dd0-41f9-8bd0-557196a44ecf | null
     ed584e99-80f7-4b13-9a90-9dc5571e6821 | null
    
    (3 rows)
  • Add a column favorite_color of varchar, and then change the data type of the same column to text.
    ALTER TABLE cycling.cyclist_alt_stats ADD favorite_color varchar;
    ALTER TABLE cycling.cyclist_alt_stats ALTER favorite_color TYPE text;
    Note: There are limitations on altering the data type of a column. The two data types, the original and the one changing to, must be compatible.