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: (view raw, whole thread or download thread mbox)
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
  select updatedTime from <table> ....

In order to overcome this, we created our views like this:
    updatedTime AS "updatedTime",

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

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


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-2017 The PostgreSQL Global Development Group