Rename column only if exists

postgresql ddl alter-table
NessBird picture NessBird · Jan 4, 2018 · Viewed 10.1k times · Source

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

Nikunj Satasiya picture Nikunj Satasiya · Nov 20, 2019

HI Try This Solution.

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

Related questions

Renaming multiple columns in one statement with PostgreSQL

Is it possible to rename multiple columns in a single statement, something along the lines of: ALTER TABLE Users RENAME COLUMN userName TO user_name, RENAME COLUMN realName TO real_name;

Read More
How to alter "REFERENCES" in PostgreSQL?

How can I alter the reference to a table in PostgreSQL when the table name has been changed? Say I have: CREATE TABLE example1 ( id serial NOT NULL PRIMARY KEY, name varchar(100) ); CREATE TABLE example2 ( id serial NOT NULL PRIMARY …

Read More
Change column type from timestamp WITHOUT time zone to timestamp WITH time zone

I found this ALTER COLUMN statement in the PostgreSQL 9.3 ALTER TABLE manual page: ALTER TABLE audits ALTER COLUMN created_at SET DATA TYPE timestamp with time zone USING timestamp with time zone 'epoch' + created_at * interval '1 second'; I cannot …

Read More