Thread: Re: bugs in Query tool... case sensitivity conflict with the rest of PgAdmin created object... explicit schema required

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of BillR
> Sent: 28 November 2005 22:59
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] bugs in Query tool... case
> sensitivity conflict with the rest of PgAdmin created
> object... explicit schema required
>
> OS: Windows 2000
> PgAdmin III V 1.4
> English
> Binary (With PostgreSQL 8.1)
>
> First:  Thanks for making PgAdmin.  It has prompted me to use
> PostgreSQL
> much more.

You're welcome.

> Issues and steps to create the issues follow:
>
> 1) There is a conflict between how the 'automated' tools work and how
> the Query tool works (minor, but can significantly impact usability)
> with respect to case sensitivity:
>     1.1) The 'automated' tools do not change the case of the objects
> created (including tables, schemas, columns, etc.).  E.g. create a
> schema by right clicking schemas and selecting 'New Schema'. You can
> create a schema named: 'WorkSchema' for instance. It is case
> sensitive.
>     1.2) The manual tool (i.e. the Query tool) *changes* the
> case of the
> objects created.  It tries to be case insensitive. E.g. the DDL
> statement 'create table WorkSchema.mytable' fails as the query tool
> changes everything to lower case and thus is looking for the
> 'workschema' schema... not the correct 'WorkSchema'.
>          1.2.1)  This causes the conflict/issue when working with
> objects created with the automated tools from within the Query tool.

This is intentional behaviour. The graphical tools allow you to create
object by hiding you from the details of the SQL queries required. This
includes quoting identifiers.

The Query Tool on the other hand is designed to allow you to write and
test arbitrary SQL queries, often before using them in your own repports
or applications. We intentionally do not modify the SQL you write here
in any way, otherwise you might easily run into unexpected problems when
you move your work into your own code (for example).

> Suggested Fix:
> The automated tools and the Query tool should be brought in line to
> handle the case of object names in a consistent manner.
>
> The second issue *might* fall under the category 'required for next
> version'... sort of... ;-)
>
> 2) It is not apparent that in the Query tool you need to explicitly
> create your objects within a specific schema using the dot notation
> (e.g. 'create schema.table <name>'

As I noted above, the query tool is designed to allow you to execute
arbitrary SQL queries on the server. Depending on the schema search
path, you may need to prepend the schema name to some objects, however
this is no different from how it would work in psql or any other
application. Whilst technically we could set the search path based on
the treeview object selected at the time the window is opened, this
strikes me a being a bad design (breaking the principle of least
surprise) as it would mean that the queries you have written suddenly
only work when you've opened the query tool from a specific place. That
would certainly cause me more problems than it would ever resolve, as
well as most other users I suspect.

I would suggest simply modifying the search path in the first statement
of your query if that is important to you.

Regards, Dave.