Skip site navigation (1) Skip section navigation (2)

Difficulties Storing Case Sensitive DDL Definitions

From: "Lane Van Ingen" <lvaningen(at)esncc(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Difficulties Storing Case Sensitive DDL Definitions
Date: 2005-11-15 21:42:50
Message-ID: EKEMKEFLOMKDDLIALABIKEBFCFAA.lvaningen@esncc.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.



Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2005-11-15 21:54:28
Subject: Re: Difficulties Storing Case Sensitive DDL Definitions
Previous:From: Chris BrowneDate: 2005-11-15 17:12:24
Subject: Re: Application using PostgreSQL as a back end (experienced programmers please)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group