For anyone who follows the JDBC mailing list, there was a discussion over
the last day or so about the casing of identifiers in Postgres and SQL92.
I failed to realize that when a table is created with quotations around the
identifiers and the case is mixed, you must then refer to that idenifier
evermore with the same casing sealed in double quotes.
Initially I used pgamin's wizard to create my tables. When I realized what
was happening with the quotes and the capitalization, I dropped the tables,
and reran the create table queries sans quotes. The thing is, pgadmin still
seemed to create the tables using the quotes, depite my arbitrary SQL not
containing any. To achieve the desired affect, I dropped the tables once
again, and recreated the tables with psql.
Now, if the wizard wants to use quotes in creating tables, I say 'Wave your
magic wand away!' But, for the little green monster to 'helpfully' place
quotes around identifiers when executing my SQL makes me want to execute
him.
Also, now that I have created my tables with psql without the quotes, and my
servlet is running happily without identifiers needing to be enclosed in
quotes, as I view a table's SQL in the definition pane, I think it would be
useful to see the quotes only when appropriate, considering the behavior of
Postgres when "'s are used in table creation vs. when they are not used.
Of course it is possible I'm misinterpreting what's going on, but my
explanation seems to fit the behavior I've observed, and what I've read over
the last day or so about the casing system.
Colin Freas