Thread: Difficulties Storing Case Sensitive DDL Definitions
We are in the process of converting a legacy application to PostgreSQL, using Windows 2003, version 8.0.1. We have been noticing (via pgAdmin) that when we create a view, PostgreSQL appears to 'flatten' all of our DDL statements to lowercase. Because the legacy code is messy and undocumented, and because it uses names that are a mixture of uppercase and lowercase, we felt it would be a better to create a separate schema with views of the same name as the tables we are converting, and use the view to return rows to the app that have the mixture of upper- and lowercase letters it desires. For instance, here is a field that we tried to create in a view: as input by our CREATE VIEW statements : updatedTime as stored in PostgreSQL, and viewed by pgAdmin : updatedtime The application is issuing a query statement that wants 'updatedTime', like this: select updatedTime from <table> .... In order to overcome this, we created our views like this: CREATE VIEW <view_name> AS SELECT updatedTime AS "updatedTime", : (etc.) When trying to query it (via pgAdmin and other tools), we found we had to quote the field names to avoid syntax errors, like this select "updatedTime" from <table> .... This means we have to go back and change all queries in the legacy application if we use this approach, which is exactly what we were hoping to avoid. QUESTION: Is there any way around this behaviour of 'flattening' the case of schema objects? Don't see any config parms or run-time options that seem to apply.
On 11/15/05 4:42 PM, "Lane Van Ingen" <lvaningen@esncc.com> wrote: > We are in the process of converting a legacy application to PostgreSQL, > using Windows 2003, version 8.0.1. > > We have been noticing (via pgAdmin) that when we create a view, PostgreSQL > appears to 'flatten' all of our DDL statements to lowercase. Because the > legacy code is messy and undocumented, and because it uses names that are a > mixture of uppercase and lowercase, we felt it would be a better to create a > separate schema with views of the same name as the tables we are converting, > and use the view to return rows to the app that have the mixture of upper- > and lowercase letters it desires. > > For instance, here is a field that we tried to create in a view: > as input by our CREATE VIEW statements : updatedTime > as stored in PostgreSQL, and viewed by pgAdmin : updatedtime > The application is issuing a query statement that wants 'updatedTime', like > this: > select updatedTime from <table> .... > > In order to overcome this, we created our views like this: > CREATE VIEW <view_name> AS SELECT > updatedTime AS "updatedTime", > : > (etc.) > > When trying to query it (via pgAdmin and other tools), we found we had to > quote the field names to avoid syntax errors, like this > select "updatedTime" from <table> .... > This means we have to go back and change all queries in the legacy > application if we use this approach, which is exactly what we were hoping to > avoid. > > QUESTION: Is there any way around this behaviour of 'flattening' the case of > schema objects? Don't see any config parms or run-time options that seem to > apply. You need to use quotes EVERYWHERE if you want to have a mixture of uppercase and lowercase. Otherwise, you can just use lowercase and have everything case-folded. So, as far as I know, there is no such configuration option. Sean
Lane Van Ingen wrote: > We are in the process of converting a legacy application to PostgreSQL, > using Windows 2003, version 8.0.1. > > We have been noticing (via pgAdmin) that when we create a view, PostgreSQL > appears to 'flatten' all of our DDL statements to lowercase. Because the > legacy code is messy and undocumented, and because it uses names that are a > mixture of uppercase and lowercase, we felt it would be a better to create a > separate schema with views of the same name as the tables we are converting, > and use the view to return rows to the app that have the mixture of upper- > and lowercase letters it desires. > > For instance, here is a field that we tried to create in a view: > as input by our CREATE VIEW statements : updatedTime > as stored in PostgreSQL, and viewed by pgAdmin : updatedtime > The application is issuing a query statement that wants 'updatedTime', like > this: > select updatedTime from <table> .... > > In order to overcome this, we created our views like this: > CREATE VIEW <view_name> AS SELECT > updatedTime AS "updatedTime", > : > (etc.) > > When trying to query it (via pgAdmin and other tools), we found we had to > quote the field names to avoid syntax errors, like this > select "updatedTime" from <table> .... > This means we have to go back and change all queries in the legacy > application if we use this approach, which is exactly what we were hoping to > avoid. > > QUESTION: Is there any way around this behaviour of 'flattening' the case of > schema objects? Don't see any config parms or run-time options that seem to > apply. > > I had a similiar problem with my scripts using Perl. What I found is that it makes no difference at all how the application calls the elements if quotes are not used in the elements as everything will be lowercased and used from there. However the trick was to have the queries quoted so that they would return row names as the script expected it. This allowed my application to get the names as required and meant minor changes had to be done to the code. I did however have to change the code slightly to enforce how the queries are formatted, but it was not as major as changing the database or the way the code worked with the database. Hopefully this workaround help at all. Martin Foster Creator/Designer Ethereal Realms martin@ethereal-realms.org