How to Rename Column If Exists in Various Databases

This will become a very common task: how to rename a column in the database table. However, before renaming, it is necessary to take measures so that the column actually exists. In the following article, there will be a delineation of how to rename a column if it exists in such popular databases like SQL Server, MySQL, PostgreSQL, Oracle, and SQLite. These steps will definitely help avoiding mistakes and keeping your database schema intact.

Table of Contents

1. Why Renaming Columns is Important
2. General Approach to Rename Columns If Exists
3. Rename Column If Exists in SQL Server
4. Rename Column If Exists in MySQL
5. Rename Column If Exists in PostgreSQL
6. Rename Column If Exists in Oracle
7. Rename Column If Exists in SQLite
8. Conclusion

Why Renaming Columns is Important

One may need to change column names for a few reasons:

• Making the code clearer: The developer needs clear and descriptive column names for the readability and maintainability of his database.
• Consistency: Following the same naming conventions for all entities in your database schema.
• Legacy Updates: Making legacy systems comply with new naming standards or business requirements.

General Approach to Rename Columns If Exists

The generic technique to rename a column if exists is as follows:

• Check for the existence of the column: It consists of specific database methods to check if columns exist.
• Rename the column: Execute the appropriate command to modify the name of the column.

Rename Column If Exists in SQL Server

You can perform a check within dynamic SQL against system catalog views, and then perform a rename. This is how it would work in SQL Server.

Syntax

IF EXISTS (SELECT * FROM sys.columns 
           WHERE Name = N'old_column_name' 
           AND Object_ID = Object_ID(N'table_name'))
BEGIN
    EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
END

Example

IF EXISTS (SELECT * FROM sys.columns 
           WHERE Name = N'FirstName' 
           AND Object_ID = Object_ID(N'Employees'))
BEGIN
    EXEC sp_rename 'Employees.FirstName', 'First_Name', 'COLUMN';
END

Rename Column If Exists in MySQL

You can check for existence of a column, then rename it, in MySQL by usingINFORMATION_SCHEMA.

Syntax

SET @table_name = 'table_name';
SET @old_column_name = 'old_column_name';
SET @new_column_name = 'new_column_name';
 
SELECT COUNT(*) INTO @exists 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @table_name 
AND COLUMN_NAME = @old_column_name;
 
IF @exists > 0 THEN
    SET @sql = CONCAT('ALTER TABLE ', @table_name, ' CHANGE ', @old_column_name, ' ', @new_column_name, ' column_definition');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;

Example

SET @table_name = 'Employees';
SET @old_column_name = 'FirstName';
SET @new_column_name = 'First_Name';
 
SELECT COUNT(*) INTO @exists 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @table_name 
AND COLUMN_NAME = @old_column_name;
 
IF @exists > 0 THEN
    SET @sql = CONCAT('ALTER TABLE ', @table_name, ' CHANGE ', @old_column_name, ' ', @new_column_name, ' VARCHAR(255)');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;

Rename Column If Exists in PostgreSQL

You could use pg_catalogto check for existence of a column and then rename it in PostgreSQL.

Syntax

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_catalog.pg_attribute 
               WHERE attname = 'old_column_name' 
               AND attrelid = 'table_name'::regclass) THEN
        EXECUTE 'ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name';
    END IF;
END $$;

Example

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_catalog.pg_attribute 
               WHERE attname = 'FirstName' 
               AND attrelid = 'Employees'::regclass) THEN
        EXECUTE 'ALTER TABLE Employees RENAME COLUMN FirstName TO First_Name';
    END IF;
END $$;

Rename Column If Exists in Oracle

First, you can check if a column exists using theUSER_TAB_COLUMNSin Oracle and then change it.

Syntax

BEGIN
    IF EXISTS (SELECT 1 FROM USER_TAB_COLUMNS 
               WHERE TABLE_NAME = 'TABLE_NAME' 
               AND COLUMN_NAME = 'OLD_COLUMN_NAME') THEN
        EXECUTE IMMEDIATE 'ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name';
    END IF;
END;

Example

BEGIN
    IF EXISTS (SELECT 1 FROM USER_TAB_COLUMNS 
               WHERE TABLE_NAME = 'EMPLOYEES' 
               AND COLUMN_NAME = 'FIRSTNAME') THEN
        EXECUTE IMMEDIATE 'ALTER TABLE Employees RENAME COLUMN FirstName TO First_Name';
    END IF;
END;

 Rename Column If Exists in SQLite

 In SQLite, renaming a column is much more complicated because there is no support for the syntax ofALTER TABLE RENAME COLUMNitself. You do: 

1. Check whether a column exists.
2. Create a new table with your desired column name.
3. Copy all data from the old one to the new one.
4. Drop the old table.
5. Rename the new table to the name of the original table.

Syntax

PRAGMA foreign_keys=off;
 
-- 1. Create a new table with the new column name
CREATE TABLE new_table AS SELECT old_column_name AS new_column_name, * FROM old_table;
 
-- 2. Drop the old table
DROP TABLE old_table;
 
-- 3. Rename the new table to the original table name
ALTER TABLE new_table RENAME TO old_table;
 
PRAGMA foreign_keys=on;

Example

PRAGMA foreign_keys=off;
 
-- 1. Create a new table with the new column name
CREATE TABLE Employees_New AS SELECT FirstName AS First_Name, * FROM Employees;
 
-- 2. Drop the old table
DROP TABLE Employees;
 
-- 3. Rename the new table to the original table name
ALTER TABLE Employees_New RENAME TO Employees;
 
PRAGMA foreign_keys=on;

Conclusion

Renaming columns in a database if they exist is a common task that requires careful handling to avoid errors and ensure data integrity. Each database system has its own methods and best practices for performing this operation. By following the examples provided for SQL Server, MySQL, PostgreSQL, Oracle, and SQLite, you can efficiently manage your database schema and maintain consistency.