Rename Column Only If Exists in PostgreSQL

watch_later 28 May, 2021

In this article, I am going to explain how to rename the column only if a column exists within the table in PostgreSQL. Here, I'll also explain how to create a table as well as a way to check whether the specified column exists or not in the information_schema, and based on that we will rename the column in the PostgreSQL.

Rename Column Only If Exists in PostgreSQL

In my previous article, I explained, Query to Generate Parent-Child Relation Rows By Splitting String in SQL Server and PostgreSQL that you might like to read.

I noticed, many developers/programmers/people especially who are beginners or students, working with the PostgreSQL database, sometimes they getting difficulty while they going to rename or alter any specified column 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'll explain how you can rename the column only if the specified column exists in your PostgreSQL database table with a simple and issue example.

Requirement

  1. Create a Sample table with a few columns in PostgreSQL.
  2. Write a script to rename any single column within the created table.

Implementation

So, let's take an example to rename the column only if a column exists within the table in PostgreSQL. Here, we will check the specified name of the column is exist or not in the information_schema of the PostgreSQL database, only If we found the specified column exists then we will rename the specified column.

Here, we will create a sample table in PostgreSQL, and then write a script to rename the column if only a column exists.

Create 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.

The syntax for Rename Column

So, first, let us understand the syntax to rename the specified column 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 $$;

Explanation

As you can see in the written syntax above, here we have used the command IF EXISTS, where we have checked the specified column is available or not in the  information_schema.columns table. If the system will found such a column then this condition will get true and will execute ALTER statement and rename the specified column. Now, let's understand with an example.

Rename 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 $$;

Explanation

As I explained in the syntax of rename column, here we have checked the column id of the created table user_accounts is exists in the information_schema.columns table or not, if the system will found that the column id is found in the information_schema.columns then system will execute the ALTER TABLE statement and rename the column id with the name user_id.

Summary

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.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon