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