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.


2 Answers 2


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;
  • 1
    This data is coming from openstreetmap, and that's how the schema gets imported to postgres. It is annoying. Commented Jan 15, 2014 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.
    – user1822
    Commented Jan 15, 2014 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. Commented Jan 15, 2014 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.
    – user1822
    Commented Jan 15, 2014 at 13:36
  • renaming the columns in the source table is not an option because they are built into other tools (legacy stuff). Commented Jan 15, 2014 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? Commented Jan 17, 2014 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.