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?

Answer 1

Please read this article for a detailed explanation.

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 $$; 

Answer 2

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.

Answer 3

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;

Answer 4

@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.