Rename Column If Exists | PostgreSQL

On this article, I’ll clarify learn how to rename the column provided that a column exists inside the desk in PostgreSQL. Right here, I will additionally clarify learn how to create a desk in addition to a approach to examine whether or not the desired column exists or not within the information_schema, and primarily based on that we’ll rename the column within the PostgreSQL.

In my earlier article, I defined, Question to Generate Father or mother-Little one Relation Rows By Splitting String in SQL Server and PostgreSQL that you simply would possibly prefer to learn.

I seen, that many builders/programmers/individuals particularly those that are learners or college students, working with the PostgreSQL database, typically they received issue after they going to rename or alter any specified column in PostgreSQL as a result of PostgreSQL doesn’t help the next command:

ALTER TABLE table_name RENAME COLUMN IF EXISTS old_columnname TO new_columnname

Right here I will clarify how one can rename the column provided that the desired column exists in your PostgreSQL database desk with a easy and problem instance.

1. Create a Pattern desk with just a few columns in PostgreSQL.
2. Write a script to rename any single column inside the created desk.

Implementation

So, let’s take an instance to rename the column provided that a column exists inside the desk in PostgreSQL. Right here, we’ll examine the desired title of the column is exist or not within the information_schema of the PostgreSQL database, solely If we discovered the desired column exists then we’ll rename the desired column?

Right here, we’ll create a pattern desk in PostgreSQL, after which write a script to rename the column if solely a column exists.

Create Desk

CREATE TABLE user_accounts (
  id serial PRIMARY KEY,
  username VARCHAR (50) UNIQUE NOT NULL,
  password VARCHAR (50) NOT NULL,
  electronic mail VARCHAR (255) UNIQUE NOT NULL,
  created_on TIMESTAMP NOT NULL,
  last_login TIMESTAMP
);

As you possibly can see within the code above, right here we now have created a desk user_accounts with just a few columns. Now, let’s rename the column id as usre_id in PostgreSQL.

The syntax for Rename Column

So, first, allow us to perceive the syntax to rename the desired 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 $$;

Clarification

As you possibly can see within the written syntax above, right here we now have used the command IF EXISTS, the place we now have checked whether or not the desired column is on the market or not within the  information_schema.columns desk. If the system finds such a column then this situation will get true and can execute ALTER assertion and rename the desired column. Now, let’s perceive with an instance.

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

Clarification

As I defined within the syntax of rename column, right here we now have checked the column id of the created desk user_accounts exists within the information_schema.columns desk or not, if the system will discover that the column id is discovered within the information_schema.columns then system will execute the ALTER TABLE assertion and rename the column id with the title user_id.

Abstract

On this article, we discovered learn how to create and the way approach to rename a specified column provided that the column exists inside the desk in PostgreSQL.