Using ALTER in PostgreSQL

Last modified: December 09, 2019

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.

Video

ALTER TABLE

Altering tables is a very common use of ALTER. Using ALTER TABLE is very useful for adding, removing, and editing columns:

ALTER TABLE traffic
ADD COLUMN nameofdriver VARCHAR;

This query will add a column called ‘nameofdriver’.

This column can be dropped by using ALTER as well. To do this:

ALTER TABLE traffic
DROP COLUMN nameofdriver;

ALTER can also be used to change the datatype of a pre-existing column. For example, you can change a boolean to a char:

ALTER TABLE traffic
ALTER COLUMN belts
TYPE char USING belts::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).

Table Constraints

Another usage of ALTER TABLE is to add table constraints. For example, if a column should be unique:

ALTER TABLE traffic
ADD CONSTRAINT unique_seqid UNIQUE (seqid);

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 traffic
DROP CONSTRAINT unique_seqid;

Renaming and Changing Schemas

ALTER TABLE can also be used to rename the table or column that is being accessed. To do this, use the rename command:

ALTER TABLE traffic
RENAME TO violations;

ALTER TABLE traffic
RENAME COLUMN dateofstop TO date;

The schema that a table is using can be changed by using:

ALTER TABLE public.traffic
SET SCHEMA mySchema;

ALTER DATABASE

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:

Name: The database can be renamed.

ALTER DATABASE [database name]
RENAME TO [new name];

Allow Connections: Whether the database allows connections to itself. NOTE: this will block all connections when true, even connections from localhost. It will need to be set to false before it can be connected to again.

ALTER DATABASE [database name]
WITH ALLOW_CONNECTIONS [true/false];

Connection Limit: Limit the number of simultaneous connections. Set to -1 for unlimited connections.

ALTER DATABASE [database name]
WITH CONNECTION_LIMIT [number of connections];

Template: Can set the database to be or not to be a template. A template is a designation that some tables get which allows the database to be copied by a user with lower privileges so that they can have a pre-structured database and fill it out with their own data.

ALTER DATABASE [database name]
WITH IS_TEMPLATE [true/false];

Owner: Can set the owner of the database. Only the current database owner and superusers can change the owner.

ALTER DATABASE [database name]
OWNER TO [username];

Tablespace: Can set which default tablespace is used. A tablespace is a way to logically separate databases on the disk so that they can handle more throughput. See the documentation for more.

ALTER DATABASE [database name]
SET TABLESPACE [new tablespace];

Configuration Parameters: Can be used to override system preferences on an individual basis. Some possible parameters that can be edited are: enable_indexscan, enable_bitmapscan, statement_timeout, and more.

ALTER DATABASE [database name]
SET [configuration parameter] TO [value];

ALTER DATABASE [database name]
RESET [configuration parameter];

ALTER DATABASE [database name]
RESET ALL;

Example:

ALTER TABLE traffic SET enable_indexscan TO OFF;

This will disable index scans on the specified database

References

Written by: Matthew Layne
Reviewed by: Matt David , Blake Barnhill