SQLite
How to Download & Install SQLite on Windows
SQLite offers a lot of different installation packages, depending on your operating systems. It...
The ALTER TABLE command is used to alter the structure of a PostgreSQL table. It is the command used to change the table columns or the name of the table.
In this tutorial, you will learn:
Here is the syntax for the PostgreSQL ALTER TABLE command:
ALTER TABLE table-name action;
The table-name parameter is the name of the table that you need to change.
The action parameter is the action that you need to perform, such as changing the name of a column, changing the data type of a column, etc.
The ALTER TABLE command changes the definition of an existing table. It takes the following subforms:
A column may be modified in a number of ways. Such modifications can be done using the ALTER TABLE command. Let us discuss these:
To add a new column to a PostgreSQL table, the ALTER TABLE command is used with the following syntax:
ALTER TABLE table-name ADD new-column-name column-definition;
The table-name is the name of the table to be modified.
The new-column-name is the name of the new column to be added.
The column-definition is the data type of the new column.
See the Book table shown below:
The table has two columns, id, and name. We need to add a new column to the table and give it the name author. Just run the following command:
ALTER TABLE Book ADD author VARCHAR(50);
After running the above command, the Book table is now as follows:
The new column was added successfully.
We can use the ALTER TABLE command to change the name of a column. In this case, the command is used with the following syntax:
ALTER TABLE table-name RENAME COLUMN old-name TO new-name;
The table-name is the name of the table whose column is to be renamed.
The old-name is the old/current name of the column.
The new-name is the new name of the column. Consider the table Book shown below:
Book:
We need the name of the column author to book_author. Here is the command:
ALTER TABLE Book RENAME COLUMN author TO book_author;
After running the command, we can view the structure of the table:
The column name was changed successfully.
We can set a default value for a column such that even when you don't specify a value for that column during INSERT operations, the default value will be used. In this case, the ALTER TABLE command can be used with the following syntax:
ALTER TABLE table-name ALTER COLUMN column-name [SET DEFAULT value];
The table-name is the name of the table whose column is to be modified.
The column-name is the name for whose default value is to be set.
The value is the default value for the column.
Consider the Book table given below:
We need to set a default value for the book_author column. We can run the following command:
ALTER TABLE Book ALTER COLUMN book_author SET DEFAULT 'Nicholas Samuel';
Now, let us insert a row into the table:
INSERT INTO Book (id, name) VALUES (6, 'PostgreSQL for Beginners');
Note that we inserted values for only two columns, id and name. However, the default value has been used for book_author column:
A check constraint helps in validating the records that are being inserted into a table. We can do this by combining the ALTER TABLE command with the ADD CHECK statement. Syntax:
ALTER TABLE table-name ADD CHECK expression;
The table-name is the name of the table to be altered.
The expression is the constraint to be imposed on the table column.
Let us modify the book_author column of the Book table so that it only accepts the values, Nicholas and Samuel:
ALTER TABLE Book ADD CHECK (book_author IN ('Nicholas', 'Samuel'));
Now, let us try to insert a value other than Nicholas or Samuel into the book_author column of the Book table:
INSERT INTO Book VALUES(7, 'Best PostgreSQL Book', 'Gregory Bush');
The statement will return the following error:
The insert operation failed because we violated the check constraint.
Here is the syntax for the ALTER TABLE command for renaming a table:
ALTER TABLE table-name RENAME TO new-table-name;
The table-name is the current name of the table.
The new-table-name is the new name to be assigned to the table.
For example, let us change the name of the Book table to Books:
ALTER TABLE Book RENAME TO Books;
Now let's see how these actions can be performed using pgAdmin.
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
ALTER TABLE Book ADD author VARCHAR(50);
Step 4) Click the Execute button.
Step 5) To check whether the column was added, do the following:
The column should have been added, as shown below:
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
ALTER TABLE Book RENAME COLUMN author TO book_author;
Step 4) Click the Execute button.
Step 5) To check whether the change was successful, do the following:
The columns should now be as follows:
The column was changed successfully.
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
ALTER TABLE Book ALTER COLUMN book_author SET DEFAULT 'Nicholas Samuel';
Step 4) Click the Execute button.
Step 5) To test, run the following command on the query editor:
INSERT INTO Book (id, name) VALUES (6, 'PostgreSQL for Beginners')
Step 6) Now, we can query the table to check whether the default value was inserted in the book_author column:
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
ALTER TABLE Book ADD CHECK (book_author IN ('Nicholas', 'Samuel'))
Step 4) Click the Execute button.
Step 5) To test this, do the following:
INSERT INTO Book VALUES(7, 'Best PostgreSQL Book', 'Gregory Bush');
It will return the following:
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
ALTER TABLE Book RENAME TO Books;
Step 4) Click the Execute button.
Step 5) To check whether the table was renamed, do the following:
The table was renamed successfully.
Download the Database used in this Tutorial
SQLite offers a lot of different installation packages, depending on your operating systems. It...
What are views? VIEWS are virtual tables that do not store any data of their own but display data...
In this tutorial, we will discuss the key difference between SQL and MySQL. Before discussing SQL...
What are Loops? Loops allows a certain part of the code in a program to get executed for the...
Sorting Results Using the SELECT command, results were returned in the same order the records were...
What is SELECT query in MySQL? SELECT QUERY is used to fetch the data from the MySQL database....