In SQL, tables, databases, schemas, groups, indexes, servers, and more can be modified using the ALTER command. This command enables the user to modify a specific aspect of the table, database, group, etc. while leaving the rest of the data untouched.
There are many alterable things in postgreSQL heavily discussed in the PostgreSQL Documentation. This article will only focus on a few main uses of ALTER (ALTER table and ALTER database) for a comprehensive list, check the documentation here.
Warning: Altering tables and databases alters critical parts of their structure. As such, queries that ran on tables/databases that were altered may no longer work and may need to be rewritten.
Altering tables is a very common use of ALTER. Using ALTER TABLE is very useful for adding, removing, and editing columns:
This query will add a column called ‘nameofdriver’.
This column can be dropped by using ALTER as well. To do this:
ALTER can also be used to change the datatype of a pre-existing column. For example, you can change a boolean to a char:
This usage of ALTER takes a column and converts it into a different type using a specified method for this (in this case the cast: belts::char).
Another usage of ALTER TABLE is to add table constraints. For example, if a column should be unique:
This command can also be used to add a constraint to the whole table.
NOTE: An error will be thrown if a constraint is added to a column that already breaks that constraint (e.g. adding the UNIQUE constraint to a non-unique column will throw an error).
Common constraints include: NOT NULL, PRIMARY KEY, and UNIQUE (full list included in the documation). The constraint can also be dropped using the same command with the DROP CONSTRAINT command instead:
ALTER TABLE can also be used to rename the table or column that is being accessed. To do this, use the rename command:
The schema that a table is using can be changed by using:
Databases can also be modified using the ALTER command. There are fewer things that can be modified in a Database, however they have very serious effects. As such they often have required permissions to execute them. The things that can be changed using ALTER DATABASE are: