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;

• code base 5000: This data is coming from openstreetmap, and that's how the schema gets imported to postgres. It is annoying.
• user1822: @josh1234: why not simply rename the column then? Would probably save you a lot of trouble in the long run. Could even be automated probably.
• András Váczi: Let me add that sometimes it is easier to define the view's column names in a list: CREATE VIEW bla (col1, col2, ...) AS SELECT .... At least for automated view creation I found this easier.
• user1822: @dezso: I personally prefer to (re)name them in the select itself. That way I can immediately see which "source column" maps to which view column. If you have a complicated query it's not always easy to spot which name the query column has in the view.
• code base 5000: renaming the columns in the source table is not an option because they are built into other tools (legacy stuff).

Answer 2

You can use the following syntax (example):

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

• András Váczi: Which means you name the column twice - what's the reason behind it? Also, what is added here compared to the other answer?