Alter a table column
Use the ALTER TABLE command to add new columns, drop non-primary key columns, or rename a primary key column.
|
To change the table settings, see Altering table properties. |
Add a column
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 a column is added
Verify that the column was added with null values.
SELECT id, cyclist_age AS age FROM cycling.cyclist_alt_stats LIMIT 3;Results
id | age
--------------------------------------+------
e0953617-07eb-4c82-8f91-3b2757981625 | null
1ba0417d-62da-4103-b710-de6fb222db6f | 34
4ceb495c-55ab-4f71-83b9-81117252bf13 | 34
(3 rows)
id | age
--------------------------------------+------
e0953617-07eb-4c82-8f91-3b2757981625 | null
1ba0417d-62da-4103-b710-de6fb222db6f | 34
4ceb495c-55ab-4f71-83b9-81117252bf13 | 34
(3 rows)The result set shows the first three rows.
Rename a column
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.
Add a collection column
Alter the table cycling.upcoming_calendar to add a map named description to store a name and description for each race.
ALTER TABLE cycling.upcoming_calendar
ADD description map<text,text>;Verify the collection column
Verify the results:
SELECT * FROM cycling.upcoming_calendar WHERE year = 2015 AND month = 6;Results
Map type columns display in a JSON format.
year | month | description | events
------+-------+------------------------------------------------------------------------------+----------------------------------------------------------------------------
2015 | 6 | {'Criterium du Dauphine': 'Easy race', 'Tour du Suisse': 'Hard uphill race'} | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']
(1 rows)
year | month | description | events
------+-------+------------------------------------------------------------------------------+----------------------------------------------------------------------------
2015 | 6 | {'Criterium du Dauphine': 'Easy race', 'Tour du Suisse': 'Hard uphill race'} | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']
(1 rows)
year | month | description | events
------+-------+------------------------------------------------------------------------------+----------------------------------------------------------------------------
2015 | 6 | {'Criterium du Dauphine': 'Easy race', 'Tour du Suisse': 'Hard uphill race'} | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']
(1 rows)See also:
