In this article, I am going to explain how to rename a column only if there is a column within the table in PostgreSQL. Here, I will also explain how to create a table as well as a way to check if the specified column exists or not in info_schema, And based on this we will change the column name in PostgreSQL.
In my previous article, I explained, a query to create parent-child relationship lines by splitting a string in SQL Server and PostgreSQL that you might want to read.
I noticed, many developers / programmers / people especially who are beginners or students, working with the PostgreSQL database, sometimes they have difficulty while going to rename or change any column specified in PostgreSQL because PostgreSQL does not support the following command:
ALTER TABLE table_name RENAME COLUMN IF EXISTS old_columnname TO new_columnname
So here I will explain how you can rename the column only if the specified column exists in your PostgreSQL database table with a simple and problematic example.
- Create a sample table with several columns in PostgreSQL.
- Write a script to rename each individual column in the created table.
So, let’s take an example of renaming a column only if there is a column within the table in PostgreSQL. Here, we will check whether the specified column name exists or not in the PostgreSQL database information_schema, only if we found that the specified column exists then we will change the specified column name.
Here, we will create a sample table in PostgreSQL, and then write a script to rename the column if only one column exists.
Create a table
CREATE TABLE user_accounts ( id serial PRIMARY KEY, username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, email VARCHAR (255) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP );
As you can see in the code above, here we have created a table User accounts With few columns. Now, let’s rename the column ID as usre_id In PostgreSQL.
Syntax for rename column
So first, let’s understand the syntax for renaming the column specified in PostgreSQL.
DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='your_table' and column_name='your_column') THEN ALTER TABLE "public"."your_table" RENAME COLUMN "your_column" TO "your_new_column"; END IF; END $$;
As you can see in the syntax written above, here we used the command if exists, Where we checked that the specified column is available or not in the information_schema.columns table. If the system finds such a column then this condition will be fulfilled and executed To change A statement and rename the specified column. Now, let’s understand with an example.
Rename the column in PostgreSQL
DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='user_accounts' and column_name='id') THEN ALTER TABLE "public"."user_accounts" RENAME COLUMN "id" TO "user_id"; END IF; END $$;
As I explained in the syntax of renaming a column, here we checked the column ID of the generated table User accounts Exists in information_schema.columns Table or not, if the system finds that the column ID is in the information_schema.columns, the system will run the Change table A statement and rename the column ID With the name User ID.
In this article, we learned how to create a table as well as a way to rename a specified column only if the column exists within the table in PostgreSQL.