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 will also share an articles on postgresql monitoring tools, postgresql monitoring, psqlodbc_x64, postgresql support, oracle postgresql, redis postgres, postgresql performance, ora2pg windows, azure database for postgresql, postgresql varchar2, elasticsearch postgresql, postgresql dba, amazon aurora postgresql, tableau postgresql, aws aurora postgres, pgloader mysql to postgresql, heroku postgres plans, postgresql data warehouse, heroku postgres pricing, sql developer postgresql, azure data studio postgres, azure postgresql flexible server, redshift postgres, google cloud postgres, postgresql query optimization, postgresql online, clickhouse rename table, heroku postgres, aws postgres pricing, timescale db, postgresql, psql, docker postgres, pgsql, python postgresql, psql list databases, postgres list databases, postgrest, postgres database, postgresql insert, postgresql windows, postgresql version, django postgresql, postgres db, postgresql ubuntu, postgresql with, postgres use database, postgresql 13, postgresql documentation, jdbc postgresql, postgresql if, postgresql server, postgresql cluster, postgresql mac, postgresql 12, php postgresql, psql use database, postgresql 14, postgresql linux, dockerhub postgres, postgres now, dbeaver postgresql, mysql postgresql, bitnami postgresql, psql insert, sqlalchemy postgresql, postgres any, postgres admin, maven postgresql, in postgresql, laravel postgresql, postgresql latest version, postgresql having, postgresql query, psql windows, postgresql 9.6, c# postgresql, get postgres version, postgresql syntax, psql client, java postgresql, postgresql 10, psql version, insert query in postgresql, entity framework postgresql, postgres dockerhub, post gres, su postgres, postgresql pgadmin, postgresql w3schools, brew postgresql, postgresql cloud, postgresql sql, postgres psql, postgres bulk insert, postgresql macos, grafana postgresql, postgresql not in, postgres list all databases, postgres get version, postgresql ssl, psql mac, postgresql sample database, postgresapp, centos postgresql, postgresql client ubuntu, postgresql over, homebrew postgres, r2dbc postgresql, postgresql using, postgresql today, rpostgresql, postgresql client linux, psql query, postgres container, terraform postgres, using postgresql, citus postgres, postgres 10, sqlite to postgresql, prisma postgres, mysql workbench postgresql, psql list all databases, postgresql is, postgresql do, w3schools postgresql, postgresql as, postgresql md5, now postgresql, pl sql postgres, postgresql repository, postgresql example, postgresql 9.4, postgresql avg, psql example, postgresql remote access, postgres in memory, postgresql greatest, postgres int4, postgres meaning, sql manager for postgresql, express postgresql, azurerm_postgresql_server, postgresql raspberry pi, find postgres version, python postgresql psycopg2, postgres running queries, psql documentation, postgres in docker, pg_database, psql syntax, postgresql script, postgresql xml, java postgresql example, postgres insert example, postgres source code, postgresql davinci resolve, rds postgres pricing, postgresql year, mssql to postgresql, postgres list, postgres parameterized query, percona postgresql, terraform postgres provider, excel postgresql, postgresql brew, postgres latest, postgres clone database, psql client windows, insert query postgres, year postgresql, postgresql query example, postgresql sqlalchemy, django and postgresql, postgresql min, postgre insert into, postgresql dynamic sql, postgres js, visual studio postgresql, postgresql and, postgresql cost, postgres current version, postgresql anonymizer, postgres use db, postgresql service, azure postgresql, azure postgresql pricing, post gre, postgres uuid_generate_v4, postgresql hosting, toad for postgresql, access postgres database, elephantsql, sql server to postgresql, node postgres, psql database, local postgres database, postgresql manager, mysql to postgresql, postgresql studio, postgresql server windows, gcp postgres, digital ocean postgres, wordpress postgresql, phpmyadmin postgresql, python and postgresql, digitalocean postgres, postgresql nosql, google postgresql, postgres graphql, postgresql open source, postgresql 9.5, davinci resolve postgresql, postgresql enterprise, best postgres gui, pganalyze, online postgresql database, postgresql online editor, postgres graph database, postgresql to mysql, rds postgres, amazon rds postgres, amazon postgresql, cloud sql postgres, aurora postgresql, postgis, postgis docker, docker postgis, postgresql postgis, postgis version, postgres gis, postgis python, mongodb postgresql, postgresql workbench, postgres on kubernetes, pgwatch, postgresql client, power bi postgresql, postgresql tools, postgresql management studio, enterprisedb postgresql, mysql to postgresql converter, graphql postgres, postgresql ide, postgres kubernetes, postgresql gui, postgres enterprise manager, uuid_generate_v4 postgres, postgresql tutorialspoint, pgadmin, pgwatch2, postgresql editor, postgresql pricing, dbforge postgresql, edb postgres, rename table snowflake, aws postgres, postgres gui mac, pgmodeler, mysql to postgresql converter online, sequel pro postgres, postgresql gui windows, pgbadger, navicat for postgresql, snowflake rename table, learn postgresql, postgresql on kubernetes the right way, postgresql github, postgresql client windows, rename table in snowflake, postgresql gui client, max_worker_processes, postgresql windows 10, sql to postgresql converter, crunchy postgres, effective_io_concurrency, aurora postgres version, pgadmin4, postgres client mac, circleci postgres, psqlodbc, pgtune, pgadmin 4, postgresql gui ubuntu, dbforge studio for postgresql, list all databases in postgres, wal_buffers, postgresql online compiler, postgresql odbc, postgres_host_auth_method, aws rds postgres, pgadmin online, update column name in sql, psequel, macos postgres client, random_page_cost, aws rds postgres version, max_wal_size, wal_keep_segments, maintenance_work_mem, max_wal_senders, max_standby_streaming_delay, default_statistics_target, autovacuum_vacuum_scale_factor, autovacuum_freeze_max_age, wal_sender_timeout, autovacuum_vacuum_cost_limit, online postgresql compiler, mysql rename column, mysql change column name, rename column name in mysql, rename column name in sql, udemy postgresql, alter column name in sql, sql change column name, modify column name in sql, rename column, df rename columns, rename a column pandas, change column name pandas, dataframe rename column, pandas rename column name, column rename pandas, rename pandas column, rename dataframe column, pandas rename column, pandas column rename, rename column name in pandas, change column name in pandas, pandas change column name, rename pandas, rename a column in pandas, rename column python, rename column in python, rename in pandas, change column name in python, rename the column name in pandas, rename column name in python, rename columns in dataframe, column rename in pandas, replace column names in pandas, rename a column in python, change the column name in pandas, rename dataframe column in python that will make your web/software development process more easy and also helps you to upgrade your technical skills.

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.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

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

sentiment_satisfied Emoticon