Two names for a column

I have a column named foo in a table. I have constructed queries in scripts, which use this name.

I would now like to rename the column in the table to bar. This will break the scripts (there are several, but they could be changed if necessary). The table is referenced by other tables. I'd like to rename because the context of what is stored in that column has changed.

Is it possible to have two names for a column?

I will assume your table is named baz and is in schema public

create table baz (
  foo text primary key
);

insert into baz (foo) values ('hello');

select foo from baz;

Now you want to rename that column:

alter table baz rename column foo to bar;

But now your scripts are broken:

select foo from baz; -- nope!

So create a new schema, put a view in it, and alter your search_path to hide the table "behind" the view.

create schema qux;

create or replace view qux.baz as 
    select
      bar as foo
    from public.baz;

set search_path to qux, public;

select foo from baz; -- works!

This is kind of the postgres way to do create synonym

StackExchange Database Administrators Q#135354, answer score: 8