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 int;
-
Rename the
id
primary key column tocyclist_id
.ALTER TABLE cycling.cyclist_alt_stats RENAME id TO cyclist_id;
Restriction: Only primary key columns can be renamed.