Re: Casing

It's rumoured that Colin Freas once said:
>
> 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.

Hi Colin,

pgAdmin quotes identifiers because that should always work. Consider the
following examples of identifiers - on the left is the value entered, on
the right is what the PostgreSQL parser will convert it to for use as (for
example) a table name:

tablename    -> tablename
TableName    -> tablename
"tablename"  -> tablename
"TableName"  -> TableName
table name   -> [will produce an error]
Table Name   -> [will produce an error]
"table name" -> table name
"Table Name" -> Table Name

I think the important thing to note is that lower case names without
special characters like spaces are unaffected by the quotes. Future
queries may omit them or include them to no effect.

On the other hand, if you wish to use upper case or special characters,
you must *always* use quotes.

I'm not saying this is the correct behaviour as per the spec, but this is
how PostgreSQL works.

> 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.

pgAdmin doesn't modify any arbitrary SQL you enter except to remove any
comments. It certainly won't convert your hand typed:

CREATE TABLE sheep(baa text)

into

CREATE TABLE "sheep"("baa" text)

> 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.

And it would me. Nicely put btw :-)

> 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.

I have to admit I'm confused by your problem. I (and my staff) use pgAdmin
on a number of projects, and *never* quote identifiers or use identifiers
that would require quoting, yet we have no problems.

I would also point out that when PostgreSQL stores identifiers in
pg_class, pg_attribute or whereever, it doesn't store any information that
would enable you to figure out if quotes were used intially or not.

If you can provide an example of the problem you're having, I happily look
into it further for you.

Regards, Dave.