Altering a table to add a collection

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

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

Procedure

  • Alter the table cycling.upcoming_calendar to add a collection map description that can store a description for each race listed.
    ALTER TABLE cycling.upcoming_calendar ADD description map<text,text>;
  • After updating cycling.upcoming_calendar table to insert some data, description can be displayed.
    UPDATE cycling.upcoming_calendar 
    SET description = description + {'Criterium du Dauphine' : 'Easy race', 'Tour du Suisse' : 'Hard uphill race'} 
    WHERE year = 2015 AND month = 6;
    Verify the results.
    SELECT description FROM cycling.upcoming_calendar WHERE year = 2015 AND month = 6;
    Map type columns display in a JSON format.
     description
    ------------------------------------------------------------------------------
     {'Criterium du Dauphine': 'Easy race', 'Tour du Suisse': 'Hard uphill race'}
    
    (1 rows)