Modify table columns

Add, drop, or rename table columns.

Use the ALTER TABLE command to add new columns, drop non-primary key columns, or rename a primary key column.

Tip: To change the table settings, see Altering table properties.

Procedure

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

    The ALTER TABLE command creates the column metadata, 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, cyclist_age AS 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)
  • Drop the cyclist_age column from the table.
    ALTER TABLE cycling.cyclist_alt_stats 
    DROP cyclist_age;
  • Rename the id primary key column to cyclist_id.
    ALTER TABLE cycling.cyclist_alt_stats 
    RENAME id TO cyclist_id;
    Restriction: Only primary key columns can be renamed.