Rename Column Only If Exists in PostgreSQL

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.

demand

1. Create a sample table with several columns in PostgreSQL.
2. Write a script to rename each individual column in the created table.

Implementation

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 does not exist 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 $$;

explanation

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

explanation

As I explained in the syntax of the rename 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.

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.