PostgreSQL Rename Column

In this PostgreSQL tutorial, we will learn about the “Postgresql rename column” and cover the following topics.

PostgreSQL rename column
PostgreSQL rename column if exists
PostgreSQL rename column in select
PostgreSQL rename column to lowercase
PostgreSQL rename column in view
PostgreSQL rename column script

Table of Contents

In PostgreSQL, RENAME COLUMN is used in ALTER TABLE statement to rename the column of the table.

Syntax:

ALTER TABLE table_name 
RENAME COLUMN old_column_name TO new_column_name;

Let’s create a table named rename_col that we will use in this tutorial.

CREATE TABLE rename_col(id SERIAL,NAME VARCHAR,MARKS FLOAT);

Insert the following records in the table rename_col.

INSERT INTO rename_col(NAME,MARKS)VALUES('Andrew D. Reyes',30),('Dana W. Maguire',67),
('Gerard B. Perez',60),('Curtis J. Dunning',80),('Daniel M. Threlkeld',90);
SELECT * FROM rename_col;

The output of the above table is given below.

Let’s rename the column id to roll_no in a table rename_col using the below code.

ALTER TABLE rename_col
RENAME COLUMN id TO roll_no;

SELECT * FROM rename_col;

In the above code, we are renaming the column name id to roll_no using the RENAME COLUMN, specifying the name of the old column name (id) that we want to change, then the new column name (roll_no) after the RENAME COLUMN keywords.

The output of the above code is given below.

In the above output, we can see the column name id changed to roll_no.

This is how to rename a column in PostgreSQL.

Read Postgresql replace + Examples

PostgreSQL rename column if exists

In Postgresql, Before renaming a column we can check whether the column exists or not using the procedural language.

Run the below code to rename the column name to st_name if exists.

DO $$
BEGIN
  IF EXISTS(SELECT *
    FROM information_schema.columns
    WHERE table_name='rename_col' and column_name='name')
  THEN
      ALTER TABLE "public"."rename_col" RENAME COLUMN "name" TO "st_name";
  END IF;
END $$;

SELECT * FROM rename_col;

The output of the above code is given below.

As we can see in the above output, the column name changed to st_name.

This is how to rename column if exists in PostgreSQL.

Read Postgresql unique constraint

PostgreSQL rename column in select

In Postgresql, the column can be renamed in the SELECT statement by giving only a new name.

Let’s understand through an example.

SELECT roll_no st_id,st_name student_name,marks score FROM rename_col;

In the above code, we are renaming the column name using the method call aliases or alternate names.

In aliases, we provide the new column name after the old column name like (roll_no st_id) in the SELECT statement with or without AS keyword between the old column name and the new column name.

The output of the above code is given below.

This is how to rename columns in select statement in PostgreSQL.

Read PostgreSQL Update Join

PostgreSQL rename column to lowercase

In Postgresql, we can rename the column name to lowercase, or Postgresql renames the column name to lowercase automatically.

First, we will convert the column name to uppercase using the below code.

ALTER TABLE rename_col
RENAME COLUMN student_name TO "STUDENT_NAME";
SELECT * FROM rename_col;

In the above code first, we are renaming the column student_name to uppercase using the double quotes around the new column name “STUDENT_NAME”.

Note, if we use a single quote like ‘STUDENT_NAME’ in the above code, it will not work.

The output of the above code is given below.

From the above output, we have changed the column name from student_name to STUDENT_NAME in uppercase.

Now, change the uppercase column to lowercase using the below commands.

\t on

The \t on will enable the rows-only mode in the psql prompt.

SELECT 'ALTER TABLE '||'"'||table_name||'"'||' RENAME COLUMN '||'"'||column_name||'"'||' TO ' || lower(column_name)||';' 
FROM information_schema.columns 
WHERE table_schema = 'public' and lower(column_name) != column_name

In the above code, information_schema.columns allow us to access the all table in the Postgresql database.

\g 'D:\\lower_col' - It is used to execute the last command

\i 'D:\\lower_col' - This one is used to execute the command from a file

Postgresql rename column to lowercase code

The output of the above code is given below.

This is how to rename column to lowercase in PostgreSQL.

Read PostgreSQL Subquery with Examples

PostgreSQL rename column in view

In Postgresql, the column in a view can also be renamed using the RENAME COLUMN in ALTER TABLE statement.

If you don’t about “What is the view?” then refer to another tutorial How to create a view in PostgreSQL

First, let’s create the view named rename_view.

CREATE VIEW rename_view AS SELECT * FROM rename_col;
SELECT * FROM rename_view;

In the above view, we are going to rename the marks column to score.

ALTER TABLE rename_view 
RENAME COLUMN marks TO score;
SELECT * FROM rename_view;

The output of the above code is given below.

In the above output, we have changed the marks column to score in a view named rename_view.

This is how to rename a column in view in PostgreSQL.

Read Postgresql Joins – How to use

PostgreSQL rename column script

In Postgresql, the column can be renamed using the script, the script is a file where we write the SQL queries that we want to execute and the file is saved with extension (.sql).

If you don’t know about the Postgresql script then visit our other tutorial Postgresql import SQL file + Examples

First, let’s see the column name of rename_col.

Open the notepad or any text editor on your computer and write the below query or code in that text editor. Save the file with the extension (xyz.sql).

ALTER TABLE rename_col
RENAME COLUMN marks TO score;

SELECT * FROM rename_col;

I have created a script with the name rename.sql.

Now open psql on your computer execute the below script file using the below code.

\i 'D:\\rename.sql'