Jan Zyka Jan Zyka - 4 months ago 9
SQL Question

H2 table columns doesn't respect case when double-quoted

I'm developing a tool which imports data into on-the-fly generated schema. Therefore I have little control over what the table or column names will look like. I recently run into an issue with creating 2 columns in the table which have identical name but different case. The problem could be demonstrated on this simplest DDL operation:

CREATE TABLE "a" (
"c1" integer,
"C1" integer
)


This fails for me in the program as well as h2 console with:


Duplicate column name "C1"; SQL statement: create table "a" ( "c1"
integer, "C1" integer )


This is working fine in PostgreSQL for example and I think it should work as the SQL specification requires case sensitivity when values are double-quoted.

I'm currently using in-memory h2 database.

EDIT: It runs out the reason was the connection was established with option
DATABASE_TO_UPPER=false
which caused this behaviour

Answer

In H2 Names are not case sensitive.

H2 docs

...but...

Quoted names are case sensitive, and can contain spaces.

H2 docs of Quoted Names

I just ran this exact example in a local instance of H2 and it worked fine.

create table p ("c1" integer, "C1" integer);
insert into p values (1,2);
select * from p;

I think your SQL is being changed before it runs in H2. Everything may be being capitalized before the SQL is run.

Per your update the connection was established with option DATABASE_TO_UPPER=false which caused this behaviour

Comments