Postgresql – Two names for a column

database-designpostgresql

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?

Best Answer

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

Related Solutions

Table contains multiple names for same company

In this case I'd create a new table called CompanyAlias that will contain all possible aliases that you get for all companies. A possible structure would be:

CompanyAlias:

AliasId - int - Primary Key - some generated id
Alias - varchar(100) - the name of the alias
CompanyID - int - Foreign Key - references the original table of companies (in case you don't have one, I believe you should create one table with all unique companies and any other details that you might get)
any other column you'd like to have (description, creator..etc)

A query that will bring all the transactions for the main company of a given alias could look like:

declare @alias varchar(100);
set @alias = 'MicroS';

Select t.TransactionId, t.Alias, c.Company
from Transaction t
join CompanyAlias ca on ca.Alias = t.Alias
join Company c on c.CompanyId = ca.CompanyId
where t.Alias in
(
    Select ca.Alias
    from CompanyAlias ca
    where ca.CompanyId = 
    (
        Select ca.CompanyId
        from CompanyAlias ca
        where ca.Alias = @alias
    )
);

Where the tables would be:

Transaction - contains monetary transactions (should contain company alias used by client)
CompanyAlias - defined earlier
Company - defined by you to have unique companies and their main details

PS: in case the SELECT statement is too bulky (written in notepad), I want to mention that I wanted to:

get the CompanyId of the searched alias (the one that you specify in the search form)
get all the aliases of the mentioned CompanyId
get all the transactions of the mentioned aliases

Postgresql – Postgres 9.2 – Create View Different Column Names

addr:housename is an illegal column name to begin with. You could have only created that if you used a quoted identifier (see the manual for details)

To create a view and rename the column, simply provide a column alias in the view's query:

create or replace view v_some_view
as
select ... other columns ...
       "addr:housename" as addr_housename
from some_table;

Best Answer

Related Solutions

Table contains multiple names for same company

Postgresql – Postgres 9.2 – Create View Different Column Names

Related Question