bugs in Query tool... case sensitivity conflict with the rest of PgAdmin created object... explicit schema required - Mailing list pgadmin-support

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.

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. 

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>'  
Suggested Fix:
a) When opening the Query query tool, it should note the currently 
highlighted schema in the navigation panel on the left and automatically 
reference that schema when opened.
b) The Query tool should also display the schema that is receiving the 
focus of the commands being run.  That is, the schema context in which 
it is currently running.
c) As a suggestion, when in the Query tool, you could make the schema 
context be an item displayed in a drop down selection box so that the 
users could change the schema context without moving from the query tool.


Steps to create the issue:

1)  I created a schema called "WorkSchema"  (NOTE the letter case... 
capital W, capital S) using the automated tools (i.e. right click on the 
'Schema', selected 'New Schema ).
2)  While the WorkSchema is highlighted in the navigation panel, I 
opened the Query tool and manually create a table called "PERSON"

CREATE TABLE PERSON(
PER_ID NUMERIC (5, 0) NOT NULL,
PER_FIRST_NAME VARCHAR (40) NOT NULL,
PER_LAST_NAME VARCHAR (40) NOT NULL,
PER_BIRTH_DATE DATE ,
PER_WEIGHT_KG NUMERIC (4, 2) NOT NULL,
PER_HEIGHT_M NUMERIC (4, 2) NOT NULL,
PRIMARY KEY (PER_ID)
);

3)  Result said table created
4)  I couldn't find the table under the WorkSchema schema. (it was 
actually made in the 'public' schema for some reason).   4a) This is what lead me to find the case sensitivity issue
below).
5)  In the query tool I ran the command DROP TABLE PERSON;
6)  I then tried to create the table again, *explicitly* using the 
schema name in the create statement.


CREATE TABLE WorkSchema.PERSON(
PER_ID NUMERIC (5, 0) NOT NULL,
PER_FIRST_NAME VARCHAR (40) NOT NULL,
PER_LAST_NAME VARCHAR (40) NOT NULL,
PER_BIRTH_DATE DATE ,
PER_WEIGHT_KG NUMERIC (4, 2) NOT NULL,
PER_HEIGHT_M NUMERIC (4, 2) NOT NULL,
PRIMARY KEY (PER_ID)
);

7)  I got the error message:  schema 'workschema' does not exist.
8)  I dropped the schema WorkSchema using the automated tools (right 
clicked on the schema, Drop cascaded).
9)  I re-created the schema but named it instead, 'workschema' using ALL 
lower case (lower case w lower case s), using the 'automated' tools 
(i.e. right click on the 'Schema', selected 'New Schema )
10) I created the table again using:

CREATE TABLE workschema.PERSON(
PER_ID NUMERIC (5, 0) NOT NULL,
PER_FIRST_NAME VARCHAR (40) NOT NULL,
PER_LAST_NAME VARCHAR (40) NOT NULL,
PER_BIRTH_DATE DATE ,
PER_WEIGHT_KG NUMERIC (4, 2) NOT NULL,
PER_HEIGHT_M NUMERIC (4, 2) NOT NULL,
PRIMARY KEY (PER_ID)
);

11)  Success. Table created in the 'workschema' schema.



Regards,

Bill Rosmus