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.


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
select ... other columns ...
       "addr:housename" as addr_housename
from some_table;
  • This data is coming from openstreetmap, and that's how the schema gets imported to postgres. It is annoying. – code base 5000 Jan 15 '14 at 13:24
  • 1
    @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. – a_horse_with_no_name Jan 15 '14 at 13:28
  • 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. – dezso Jan 15 '14 at 13:33
  • @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. – a_horse_with_no_name Jan 15 '14 at 13:36
  • renaming the columns in the source table is not an option because they are built into other tools (legacy stuff). – code base 5000 Jan 15 '14 at 13:37

You can use the following syntax (example):

create view v1(c_new) 
select 1 as c_old;
  • 2
    Which means you name the column twice - what's the reason behind it? Also, what is added here compared to the other answer? – dezso Jan 17 '14 at 11:11

Your Answer

