Alter Column Type in PostgreSQL

How to Alter Column Type in PostgreSQL?

We can alter column datatype in PostgreSQL by using the SET or SET DATA TYPE clause along with the ALTER TABLE and ALTER COLUMN clauses. We can also alter column type in PostgreSQL by using the pgAdmim.

using the ALTER TABLE statement in PostgreSQL

The ALTER TABLE ALTER COLUMN command is used to alter column datatype in PostgreSQL.

Syntax:

Syntax to Alter Column Type in PostgreSQL using the ALTER TABLE statement is given below:

Let us understand the clauses used in the above syntax:

• First, we will write the ALTER TABLE clause, which specifies that we want to alter the table.
• After that, we will write the name of the table whose column data type we want to modify.
• Next, the ALTER COLUMN clause is written which specifies that we want to alter the column.
• In place of columnName, we will write the name of the column whose data type we want to change.
• Next write the new data type of the column after writing TYPE or SET DATA TYPE.

Example: Let us create a table STUDENT by writing the query given below:

Now we will insert some data into the table:

Now we will execute SELECT query to check the data type of every column of the table. Query:

Output:

Suppose we want to change the data type of the address column of the student table to varchar, then we have to write the command given below:

Now after executing ALTER command, if we will try to access the data Of the table, then we can verify that the data type of column address is changed into varchar. Query:

Output:

Alter Column Type using pgAdmim

Method 1: We can modify the name, data type and constraints of columns of the table using pgAdmin also. For that right click on the name of the table and select the properties. Now the popup will open, select the columns tab and then modify the name, data type and NOT NULL constraints for the column by clicking on the icon of the edit button given for every column of the table.

Example:

1. Suppose we want to change the data type of the column of the Student table, Go to the tables section and right-click on Student.

2. Now select the properties option. After that, a pop-up will be displayed on our screen, as shown below.

3. Go to the columns tab and change the data type of the column by clicking on the edit icon as shown below in the figure.

4. Suppose we want to modify the data type of the name column, so we will click the edit icon available at the left of name.

5. Now go tothe definition tab and select the new data type for the column from the dropdown that appears after clicking on the previous data type.

6. After selecting the data type, click on the save button to save the modifications. Now your column data type is altered.

Method 2:

1. You can also alter the column Type in pgAdmin by right-clicking on the column whose data type you want to alter.

2. And then go to properties. After that popup will open.

3. Open the definition tab and alter the column type by selecting data type from the dropdown shown for the data type, as shown in the below figure.

4. After selecting data type click on the save button.

How to Alter Multiple Column Types in PostgreSQL?

Multiple ALTER COLUMN along with the ALTER TABLE command is used to modify the data type of multiple columns of the table.

Syntax:

The syntax for altering multiple Column Type in PostgreSQL is given below:

Example: Let us create a table STUDENT by writing the query given below:

Now we will insert some data into the table:

Now we will execute SELECT query to check the data type of every column of the table. Query:

Output:

Suppose we want to change the data type to varchar for the name and city column of the student table, then we have to write the command given below:

Now after executing ALTER command, if we will try to access the data Of the table, then we can verify that the data type of columns name and city is changed into varchar. Query:

Output: