Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

You can use the following syntax (example):

create view v1(c_new) 
as
select 1 as c_old;
share|improve this answer
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 at 11:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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