Case sensitivity in column name

[Sharad]

When we create tables in H2 all column names get automatically
converted to UPPER CASE.

Is there any way to avoid this behavior and permit camel case ( mixed
case ) in column names..

Commonly used database servers handle this issues awkwardly.

(1) MySQL, MS SQL and SQLite preserve the column case .
(2) Postgre converts all column names to lower case.
(3) IBM-DB2 and Oracle convert all the column names into UPPER CASE.

Is it not a good practice to preserve case in column name ?

There are many automatic Java class generation tools which create Java
class code according to table definition. They create one member
variable per column.
In this situation will not be a good practice to preserve the case of
column names and allow mix case ( camel case ) in column names.

Thanks
Sharad Kelkar

[Johann Schleier-Smith]

H2 allows you to put double quotes around column names and table names in order to preserve case.  I believe this is standard functionality in other databases as well, e.g.:

create table "testTable" ("testId" int8, "testData" varchar(50));

I have not seen camel case used commonly in SQL, but this is a stylistic matter and I don't know of good reasons why it shouldn't be used. 

              - Johann

[Thomas Mueller]

Hi,

> Is it not a good practice to preserve case in column name?

I don't really know, I thought the 'most' standard way is to convert
to uppercase.

> automatic Java class generation tools which create Java
> class code according to table definition. They create one member
> variable per column.

Don't they convert camel case to uppercase using _? Example: firstName
> FIRST_NAME. Which tool do you mean?

Regards,
Thomas

[Stephen Haberman]

> (1) MySQL, MS SQL and SQLite preserve the columncase.
> (2) Postgre converts all column names to lowercase.
> (3) IBM-DB2 and Oracle convert all the column names into UPPERCASE.

I'm running into basically the same thing.

My table DDL is generated, and it always wraps names in double quotes
to avoid colliding on keywords, e.g. "table_name". Then when writing
SQL, I just use "SELECT * FROM table_name", and it has worked fine in
postgres.

In attempting to run on H2, this statement is becoming "SELECT * FROM
TABLE_NAME", and I get a table not found exception.

So far I'm just wrapping everything in quotes by hand, e.g. "SELECT *
FROM \"table_name\""--it works but is pretty annoying, as I have to do
it for both table names and column names.

I'll keep at it, but I thought I'd pitch in agreement that the upper
case behavior is biting me--either preserving (since I type the table
names lower case) or lowering un-quoted names would be a nice
configuration option.

Thanks,
Stephen

[Takeshi]

On Mar 3, 5:57 pm, Sharad <drsskel...@gmail.com> wrote:
> Is there any way to avoid this behavior and permit camel case ( mixed
> case ) in column names..

In SQL? As on SQL-92 Sec. 5.2, everything should be upper-case:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Yes, various vendors screws things up as do they want.
But if you want to know a standard, then SQL is
case-insensitive by nature and everything is upper-case.

--
HTH, bm

[Thomas Mueller]

Hi,

> My table DDL is generated, and it always wraps names in double quotes
> to avoid colliding on keywords, e.g. "table_name". Then when writing
> SQL, I just use "SELECT * FROM table_name", and it has worked fine in
> postgres.

The same collisions are possible in CREATE and SELECT. If you quote
identifiers in the CREATE TABLE statement to avoid collisions with
keywords, then you also need to wrap it in any other type of statement
(SELECT, INSERT, DELETE and so on).

I know the list of keywords is different in each database, but I
wouldn't use double quotes because it's simpler. Just avoid keywords.
A list of common keywords is here:
http://ldbc.sourceforge.net/html/grammar.html#keywords, however there
are actually more.

Regards,
Thomas

[Stephen Haberman]

> I know the list of keywords is different in each database, but I
> wouldn't use double quotes because it's simpler. Just avoid keywords.

That sounds nicely pragmatic. I'll think about it.

Thanks,
Stephen

[witerat]

Please forgive me fi this is the second time you seen this today i'm
sure what happened to the attempt to post this.

H2 returns unquoted identifiers in lower case which looks pretty to
eye but when h2 console or SquirrelSql then wrap them in quotes cause
sql exceptions of the forms "table/schema/catalog/column not found".

On Mar 26, 8:12 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> H2 returns unquoted identifiers in lower case

Only when using the MySQL mode.

> but when h2 console or SquirrelSql then wrap them in quotes cause
> sql exceptions of the forms "table/schema/catalog/column not found".

The newest version of the H2 Console doesn't wrap them in quotes any longer.

If you use SquirrelSQL then I suggest not to use the MySQL mode.

Regards,
Thomas