Conditionally Renaming a Column if It Exists in SQL

1. Overview

It’s not uncommon to rename columns in tables when working with databases. However, we risk getting errors when trying to rename a column that doesn’t exist. Thus, we can conditionally rename a column only if it exists to avoid any errors that may arise.

In this tutorial, we’ll explore how to achieve this using different SQL databases such as MySQL and PostgreSQL. To illustrate, we’ll use the Baeldung University database schema.

2. Basics of Renaming a Column

Renaming a column entails utilizing the ALTER TABLE statement:

ALTER TABLE Department RENAME COLUMN name TO department_name;

The command above modifies the name of the column name in the Department table to department_name. However, it makes the assumption that the name column exists. Thus, before we rename the column, let’s add a condition to confirm whether it exists.

2.1. Renaming in MySQL

MySQL doesn’t support including conditional logic in a single SQL query. For instance, we can’t write a single SQL query that runs a query only if it meets a certain condition. That’s why we need to use a stored procedure, which will help us group a set of SQL commands and include conditional logic:

-- Step 1: Setting the Delimiter
DELIMITER //

-- Step 2: Creating the Procedure
CREATE PROCEDURE RenameColumnIfExists()
BEGIN
    -- Step 3: Declaring Variables
    DECLARE column_exists INT;

    -- Step 4: Checking if the Column Exists
    SELECT COUNT(*) INTO column_exists
    FROM information_schema.columns
    WHERE table_name = 'Department' 
    AND column_name = 'name';

    -- Step 5: Conditional Logic
    IF column_exists > 0 THEN
        ALTER TABLE Department RENAME COLUMN name TO department_name;
    END IF;
END //

-- Step 6: Restoring the Delimiter
DELIMITER ;

-- Step 7: Calling the Procedure
CALL RenameColumnIfExists();

This command defines a stored procedure named RenameColumnIfExists, which checks if the specified column name exists in the Department table. To explain, it queries information_schema.columns, a table that stores metadata of the columns in all tables in the database. If the name column exists, it renames the column to department_name.

2.2. Renaming in PostgreSQL

In PostgreSQL, we can use DO blocks to rename a column conditionally. We’ll use the table student and the column birth_date to illustrate:

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = 'student'
        AND column_name = 'birth_date'
    ) THEN
        ALTER TABLE student RENAME COLUMN birth_date TO date_of_birth;
    END IF;
END $$;

We execute a block of code in the procedural language plpgsql by using the DO block (DO $$ … END $$;). This procedural block ensures the commands are executed in order and only if the birth_date column exists. To be specific, the block of code checks if the student table contains the column birth_date and renames it to date_of_birth only if the column exists.

Additionally, we can declare a reusable function to accomplish the same task. Thus, we can call this reusable function with different table and column names to conditionally rename columns across different tables in the database:

CREATE OR REPLACE FUNCTION rename_column_if_exists(
    table_name TEXT, 
    old_column_name TEXT, 
    new_column_name TEXT
)
RETURNS VOID AS $$
DECLARE
    column_exists BOOLEAN;
BEGIN
    -- Verify whether the column is present in the table
    EXECUTE format('SELECT EXISTS (
        SELECT 1 
        FROM information_schema.columns 
        WHERE table_name = %L 
        AND column_name = %L
    )', table_name, old_column_name)
    INTO column_exists;

    -- If the column exists, rename it
    IF column_exists THEN
        EXECUTE format('ALTER TABLE %I RENAME COLUMN %I TO %I', table_name, old_column_name, new_column_name);
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT rename_column_if_exists('student', 'birth_date', 'date_of_birth');

Above, we create the function rename_column_if_exists, which accepts three TEXT parameters, namely table_name, old_column_name, and new_column_name. So, the function queries information_schema.columns to check whether old_column_name exists in table_name and stores the result in the variable column_exists.

If old_column_name exists, it’s renamed to new_column_name. In addition, the format function enables the safe handling of literals and identifiers to make this function secure for schema changes.

3. Conclusion

In this article, we delved into conditionally renaming a column if it exists in SQL.

Checking whether the column exists and renaming it only if it exists is necessary when conditionally renaming a column in SQL. The approach we use to accomplish this task depends on the database. When we apply the examples above, we can easily rename a column and avoid errors that may arise due to a non-existing column.