Postgres 9.2 - Create View Different Column Names

Question

I want to create a view of a table with a column renamed. I do not want to effect the source table, just the view.

I need to rename the column addr:housename to addr_housename. We have a 3rd party piece of software that does not like ':' in field names. We cannot just rename the field due to other software we use.

Can someone please provide an example on how to resolve my issue?

Thank you very much.

Answer 1

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;

Answer 2

You can use the following syntax (example):

create view v1(c_new) 
as
select 1 as c_old;