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