CODEWITHSUNDEEP
postgresql
jjah
jjah

Reputation: 71

checking if column exists and renaming it in postgresql

I want to rename the column name of table in postgresql.

I tried:

do $$
begin
if exists(select "xyz" from table_1) 
then alter table table_1 rename "xyz" to "abc"
end if;
end $$

The problem is that column "xyz" does not exist in table_1 and im getting error for it.

But I specifically need to find if column "xyz" exist and then rename it.

Is there any way which allows me to use query like (select * from table_1 where column_name="xyz") because this way the 'exists' might return true is "xyz" is in table or return false otherwise. If there is any other way to check the existence of a column and then renaming it in POSTGRESQL I am open to suggestions.

Upvotes: 1

Views: 2773

Answers (2)

Alexandre Campos
Alexandre Campos

Reputation: 290

The reason to check for that usually is to make scripts idempotent (allow them to run multiple times, if needed).

The answer from eshirvana or this other answer solves your issue in a better way, but if objects attached to your column are not important (like indexes), you can do the following

In the table farm, rename column total_area to total_size (given that only one of them exists before and that they don't have a default value)

alter table farm add column if not exists total_area int;
alter table farm add column if not exists total_size int;
update farm set total_size = coalesce(total_area, total_size);
alter table farm drop column total_area;

You can run this as many times as you want.

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

yes , here is one way :

do $$
begin
if exists (
   select 1 
   from information_schema.columns 
   where table_schema='my_schema' 
   and table_name='table_1' 
   and column_name='xyz'
)
alter table table_1 rename "xyz" to "abc"
end if;
end $$

Upvotes: 3

Related Questions