How to Change a Column Name in PostgreSQL

Every table in your PostgreSQL database has columns which are the headers and the rows that contain the records that you add. The columns are defined when creating the table, and they each have a data type assigned to it to specify the type of records that you add to the table. After creating the PostgreSQL table, you may get a change of mind and you want to change the column name later. When such a case occurs, PostgreSQL allows you to ALTER the table and set a new column name without affecting the table entries.

Detailed Guide on How to Change a Column Name in PostgreSQL

To change a column in PostgreSQL, use the following syntax:

First, specify the “table_name” that contains the column that you want to rename. Next, specify the current name of the column that you want to change. Lastly, add a new name to use for the column. Once you execute the command, you will successfully rename the column.

Let’s create a table to use for our example. We name our table as “student” which contains three columns.

Next, we insert the values into our table.

When we run the “select” command to check the current table entries, we see that our table is all set with the column names that we specified earlier.

To rename any column in our table, we use the syntax that we specified earlier. However, PostgreSQL only allows you to rename one column at a time.

For instance, if we want to rename our table’s “fname” column to “first_name”, we run the following command:

Once the command executes and you get a success message like the one in the following, it confirms that the column is successfully renamed. We can rerun our “select” statement to see how the column is. The following image shows that our first column is “first_name” which confirms that we managed to rename it.

Suppose we want to rename more than one column. We must run more than one “rename” command for each of the targets. For this example, if we want to rename the “course” and “age” columns, we run our ALTER commands as shown in the following:

To verify that our commands worked, let’s run the “select” statement. The following image confirms that we now have all the columns in our table renamed as expected:

That’s how you can quickly rename the columns in your PostgreSQL database. Feel free to utilize the logic and apply it to rename any column in your database.

Conclusion

PostgreSQL allows the users to rename the columns in their tables. Maybe you have a case where you want to use different names for your columns. Such a case requires you to use the ALTER and RENAME clauses to rename the columns in your table quickly. This post demonstrated how to rename a column in your PostgreSQL database. Hopefully, following the steps that are presented in this post helped you rename your table’s target columns.