In this article, I will 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.
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, that many developers/programmers/people especially those who are beginners or students, working with the PostgreSQL database, sometimes they got difficulty when 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
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
- Create a Sample table with a few columns in PostgreSQL.
- 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 a 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 whether the specified column is available or not in the information_schema.columns table. If the system finds 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 exists in the information_schema.columns table or not, if the system will find 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 and how way to rename a specified column only if the column exists within the table in PostgreSQL.
Tags:
- pandas rename column
- sql postgresql
- postgres rename column
- postgres rename table
- sql if
- sql exists
- postgresql create table
- sql rename column
- mysql rename column
- sql if exists
- sql rename table
- postgres add column
- alter column sql
- sql server rename column
- sql change column name
- rename column
- postgresql database
- postgres create table if not exists
- drop table if exists postgres
- create table if not exists postgres
- postgres add column if not exists
- rename column name in sql
- postgres exists
- alter table rename column
- postgres create database if not exists
- postgresql if exists
- alter column sql server
- rename table
- alter table add column postgres
- postgresql if
- table column
- postgresql server
- mysql if exists
- alter column name in sql
- postgres change column name
- rename column name in sql server
- sql server exists
- postgres change column type
- rename table sql server
- if exists sql server
- update column name in sql
- create table if not exists sql
- alter column postgres
- postgres alter column type
- modify column name in sql
- postgresql not exists
- sql change table name
- postgres check if table exists
- rename sql
- sql server create table if not exists
- sql column
- alter column name sql server
- alter column name postgres
- sql server change column name
- alter table rename column sql server
- postgres drop column if exists
- postgres if not exists
- alter table alter column sql server
- alter table rename column postgres
- postgres add column to table
- rename sql server
- using postgresql
- postgresql do
- sql alter table rename column
- sql server drop if exists
- postgres create type if not exists
- postgresql table
- if exists table sql server
- postgresql alter table change column type
- information_schema postgresql
- postgresql column type
- exists postgresql
- postgres drop if exists
- drop if exists postgres
- add column if not exists postgres
- rename column name in mysql
- postgresql sql
- change column name postgresql
- postgres create if not exists
- psql rename column
- create if not exists postgres
- drop column if exists postgres
- psql rename table
- rename column in postgresql
- if not exists postgres
- alter column datatype in postgresql
- rename column name in postgresql
- if exists in postgresql
- change column name in postgresql
- postgres select exists
- if in postgresql
- mysql alter column name
- postgres change table name
- alter table alter column postgres
- postgres drop database if exists
- sql server add column if not exists
- alter table add column if not exists postgres
- change name of column in sql
- alter table change column type postgres
- postgres rename column if exists
