PostgreSQL rename a column only if it exists

Question

I couldn't find in the PostgreSQL documentation if there is a way to run an: ALTER TABLE tablename RENAME COLUMN IF EXISTS colname TO newcolname; statement.

I would be glad we could, because I'm facing an error that depends on who made and gave me an SQL script, for which in some cases everything is perfectly fine (when the column has the wrong name, name that will actually be changed using a RENAME statement), and in other cases not (when the column already has the right name).

Hence the idea of using an IF EXISTS statement on the column name while trying to rename it. If the column has already the good name (here cust_date_mean), the rename command that must be applied only on the wrong name should be properly skipped and not issuing the following error:

db_1   | [223] ERROR: column "cust_mean" does not exist
db_1   | [223] STATEMENT: ALTER TABLE tablename RENAME COLUMN cust_mean TO cust_date_mean;
db_1   | ERROR:  column "cust_mean" does not exist

(In the meantime I will clarify things with the team so it's not a big deal if such command doesn't exist but I think it could help).

did you try the answer from [this source](https://stackoverflow.com/questions/9991043/how-can-i-test-if-a-column-exists-in-a-table-using-an-sql-statement) — Modar Na, Jul 04 '21 at 19:02
This is a feature request that is not something achievable on SO. If you want to propose it then the appropriate place is one of the [Postgres mailing lists](https://www.postgresql.org/list/), --general or --hackers. Just be aware the current Postgres development version, 14, is in feature freeze. Assuming someone would take up the proposal the earliest it could be implemented would be for version 15 which is 1+ years out. — Adrian Klaver, Jul 04 '21 at 19:02
Does it need to be a `RENAME` specifically? Do other database objects depend on that column? Notice `ADD` and `DROP` come with the `IF [NOT] EXISTS` option so you might be able to use those as a workaround. — Bergi, Jul 04 '21 at 19:35
@Bergi, the issue would be coming up with a mechanism to preserve the values from the dropped column for recovery into the added column. — Adrian Klaver, Jul 04 '21 at 20:34
@AdrianKlaver Ah, right, [that would require an `UDPATE` statement](https://stackoverflow.com/q/49368451/1048572) where again you wouldn't know whether to execute it or not… — Bergi, Jul 04 '21 at 21:09

score 5 · Answer 1 · answered Jul 05 '21 at 00:46

While there is no built-in feature, you can use a DO statement:

DO
$$
DECLARE
   _tbl         regclass := 'public.tbl';      -- not case sensitive unless double-quoted
   _colname     name     := 'cust_mean';       -- exact, case sensitive, no double-quoting
   _new_colname text     := 'cust_date_mean';  -- exact, case sensitive, no double-quoting
BEGIN
   IF EXISTS (SELECT FROM pg_attribute
              WHERE  attrelid = _tbl
              AND    attname  = _colname
              AND    attnum > 0
              AND    NOT attisdropped) THEN
      EXECUTE format('ALTER TABLE %s RENAME COLUMN %I TO %I', _tbl, _colname, _new_colname);
   ELSE
      RAISE NOTICE 'Column % of table % not found!', quote_ident(_colname), _tbl;
   END IF;
END
$$;

Does exactly what you ask for. Enter table and column names in the DECLARE section.
The NOTICE is optional.
For repeated use, I would create a function and pass parameters instead of the variables.

Variables are handled safely (no SQL injection). The table name can optionally be schema-qualified. If it's not, it's resolved according to the current search_path, just like ALTER TABLE would.

PostgreSQL rename a column only if it exists

1 Answers1

Linked