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
PostgreSQL rename column
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.
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

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'

Related PostgreSQL tutorials:
- Postgresql Delete Row
- PostgreSQL DATE Format + Examples
- PostgreSQL WHERE IN with examples
- PostgreSQL CASE with Examples
- PostgreSQL WHERE with examples
- Postgresql Add Foreign Key
So in this PostgreSQL tutorial, we have learned about the “Postgresql rename column” and covered 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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.