Rename column only if exists

Question

PostgreSQL does not allow

ALTER TABLE t RENAME COLUMN IF EXISTS c1 TO c2

...or anything like that. However, it's very convenient to be able to write scripts which modify DB structure which can be run again without first checking if it has already been run.

How do I write a PostgreSQL function to do exactly this?

What about plpgsql function checking information_schema first and then executing dynamic query renaming columns? — Tomasz Myrta, Jan 04 '18 at 20:46
Thanks for the question. How to send the requirement to PostgreSQL builders ? — Charlie 木匠, Feb 17 '21 at 17:24

Nikunj Satasiya · Accepted Answer · 2022-12-12T14:01:44.813

43

Please read this article on codingvila.com for a detailed explanation.

Rename Column Only If Exists 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 $$;

edited Dec 12 '22 at 14:01

answered Nov 20 '19 at 12:13

Nikunj Satasiya

831
9
25

This is nice and tidy...maybe I'll write it up as a function and use it. – NessBird Nov 20 '19 at 17:46
@NessBird will you please accept this answer if you got the solution? So other's can check accepted answer and get solution quick. – Nikunj Satasiya Feb 22 '21 at 13:08

Stephen Carboni · Answer 2 · 2021-04-26T09:58:02.327

You can simply handle the error that may be raised in an anonymous code block:

DO
$$
    BEGIN
        ALTER TABLE t
            RENAME COLUMN c1 TO c2;
    EXCEPTION
        WHEN undefined_column THEN RAISE NOTICE 'column t.c1 does not exist';
    END;
$$;

You can omit the text after THEN to do nothing:

DO
$$
    BEGIN
        ALTER TABLE t
            RENAME COLUMN c1 TO c2;
    EXCEPTION
        WHEN undefined_column THEN
     END;
$$;

You will probably only be getting a number when the error happens. You can find the condition name (the error name after the WHEN) from here. Make sure you are on the right version for your database.

score 3 · Answer 3 · answered Jan 06 '18 at 17:52

Better to have two functions, one calling the other:

CREATE OR REPLACE FUNCTION column_exists(ptable TEXT, pcolumn TEXT)
  RETURNS BOOLEAN AS $BODY$
DECLARE result bool;
BEGIN
    -- Does the requested column exist?
    SELECT COUNT(*) INTO result
    FROM information_schema.columns
    WHERE
      table_name = ptable and
      column_name = pcolumn;
    RETURN result;
END$BODY$
  LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION rename_column_if_exists(ptable TEXT, pcolumn TEXT, new_name TEXT)
  RETURNS VOID AS $BODY$
BEGIN
    -- Rename the column if it exists.
    IF column_exists(ptable, pcolumn) THEN
        EXECUTE FORMAT('ALTER TABLE %I RENAME COLUMN %I TO %I;',
            ptable, pcolumn, new_name);
    END IF;
END$BODY$
  LANGUAGE plpgsql VOLATILE;

score 2 · Answer 4 · answered Jul 24 '19 at 23:22

@NessBird 2 function approach is good but the Column_Exists function can be reduced to a select exists, avoiding counting, and as an SQL function instead of a plpgsql function.

create or replace function 
column_exists(ptable text, pcolumn text, pschema text default 'public')
  returns boolean 
  language sql stable strict  
as $body$
    -- does the requested table.column exist in schema?
    select exists 
         ( select null 
             from information_schema.columns 
             where table_name=ptable 
               and column_name=pcolumn 
               and table_schema=pschema
         ); 
$body$;

I added the schema parameter to handle multiple schema with the same table name. The rename_column_if_exists remains unchanged except for the possible addition of schema.

NO. See the language specification - 'SQL'. The last statement is a SQL function must be a select or DML statement with the returning clause. It return the result of the statement. Come to think of it I'm don't think a SQL function can have a return - but not 100% sure. — Belayer, Nov 20 '19 at 19:42

score 0 · Answer 5 · answered Aug 18 '21 at 09:41

Thank you everybody for the great suggestions! I've compiled a production ready solution for running migrations. I use the same function for checking if the column exists, but I've replaced the rename_table_if_exists function with a procedure:

CREATE OR REPLACE FUNCTION "product".column_exists (ptable text, pcolumn text, pschema text DEFAULT 'public')
    RETURNS boolean
    LANGUAGE sql
    STABLE STRICT
    AS $BODY$
    -- does the requested table.column exist in schema?
    SELECT
        EXISTS (
            SELECT
                NULL
            FROM
                information_schema.columns
            WHERE
                table_name = ptable
                AND column_name = pcolumn
                AND table_schema = pschema);
$BODY$;


CREATE OR REPLACE PROCEDURE "product".rename_column_if_exists (ptable TEXT, pcolumn TEXT, new_name TEXT, pschema text DEFAULT 'public')
LANGUAGE plpgsql
AS $$
BEGIN
    -- Rename the column if it exists.
    IF product.column_exists (ptable,
        pcolumn,
        pschema) THEN
    EXECUTE FORMAT('ALTER TABLE %I.%I RENAME COLUMN %I TO %I;', pschema, ptable, pcolumn, new_name);
END IF;
END
$$;

It can be used like this:

CALL <schema?>.rename_column_if_exists ('users', 'name', 'first_name', <schema name - optional>);

Rename column only if exists

5 Answers5