H2 database column name "GROUP" is a reserved word

Question

How do I create a table in H2 with a column named GROUP? I saw an example that used something like [*] a while ago, but I can't seem to find it.

Guelders

I tried with a few database (PostgreSQL, MySQL, SQLite, Apache Derby, HSQLDB). All of them reject columns named group. – Siddur

Answer 1

I've been facing the same problem recently, my table has columns "key" and "level", both of which are keywords. So instead of renaming the actual tables or bastardising the DB/configuration in any other way, just for the coughing test, the fix was to put the following in the driver configuration in application.properties:

jdbc.url=jdbc:h2:mem:db;NON_KEYWORDS=KEY,LEVEL

And beyond that, I did not have to change a thing in Hibernate/entity settings and JPA was happy and never complained again.

see details here: https://www.h2database.com/html/commands.html#set_non_keywords

Chess

Only this worked for me for both unit-tests and app itself. Thanks for sharing! – Decrypt

That saved my ass, having to deal with both PG and ORACLE and H2 cries during testing – Jardine

im with you, i dont have time to update flyway, dto mapping, etc just for a coughing test – Consequential

I can't highlight how useful this is. I tried to make rider ExpectedDataSet work with escaped table name user - but had no success. This non keyword stuff really saved me – Naumann

Answer 2

Trailing Underscore

Add a trailing underscore: GROUP_

The SQL spec explicitly promises^† that no keyword will ever have a trailing underscore. So you are guaranteed that any naming you create with a trailing underscore will never collide with a keyword or reserved word.

I name all my columns, constraints, etc. in the database with a trailing underscore. Seems a bit weird at first, but you get used to seeing it. Turns out to have a nice side-effect: In all the programming as well as notes and emails, when I see the trailing underscore I know the context is the database as opposed to a programming variable or a business term.

Another benefit is peace-of-mind. Such a relief to eliminate an entire class of possible bugs and weird problems due to keyword collision. If you are thinking, "No big deal - what's a few SQL keywords to memorize and avoid", think again. There are a zillion keywords and reserved words, a zillion being over a thousand.

---

The answer by Shiva is correct as well: Adding quotes around the name, "GROUP", does solve the problem. The downside is that remembering to add those quotes will be tiresome and troublesome.

---

Further tip: For maximum compatibility across various SQL databases, do your naming in all lowercase. The SQL spec says that all names should be stored in uppercase while tolerating lowercase. But unfortunately some (most?) databases fail to follow the spec in that regard. After hours of study of various databases, I concluded that all-lowercase gives you maximum portability.

So I actually suggest you name your column: group_

Multiple word names look like this: given_name_ and date_of_first_contact_

---

^† I cannot quote the SQL spec because it is copyright protected, unfortunately. In the SQL:2011 spec, read section 5.4 Names and identifiers under the heading Syntax Rules item 3, NOTE 111. In SQL-92 see section 5.2, item 11. Just searching for the word underscore will work.

Oak

You can quote copyrighted works; fair use allows you to quote insubstantial parts as long as you indicate the origin of the work and don't copy "substantial" amounts... – Bavardage

@Bavardage I do not employ a staff of lawyers. ANSI and ISO presumably do. – Oak

The SQL spec explicitly promises† that no keyword will ever have a trailing underscore. <- that is very important. But why do you say also about leading underscore? I mean that any naming you create with a leading or trailing underscore. – Caitlin

@Pavel_K I’ve no idea why I wrote "leading or trailing" there. I have corrected it, deleting the "leading or". The publicly-available drafts of the SQL specs I have seen promised only to avoid trailing underscore in identifiers/names. Thanks for pointing this out. – Oak

Answer 3

You have to surround the reserved word column name in quotes, like so

"GROUP"

Source (direct link): h2database.com

Keywords / Reserved Words

There is a list of keywords that can't be used as identifiers (table names, column names and so on), unless they are quoted (surrounded with double quotes). The list is currently:

CROSS, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DISTINCT, EXCEPT, EXISTS, FALSE, FOR, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, IS, JOIN, LIKE, LIMIT, MINUS, NATURAL, NOT, NULL, ON, ORDER, PRIMARY, ROWNUM, SELECT, SYSDATE, SYSTIME, SYSTIMESTAMP, TODAY, TRUE, UNION, UNIQUE, WHERE

Certain words of this list are keywords because they are functions that can be used without '()' for compatibility, for example CURRENT_TIMESTAMP.

Floorwalker

Don't do this... please. You'll hurt yourself in the end; just call it something that's not a reserved word. – Bavardage

As @Bavardage has suggested, it is not a good idea to use reserved words. You can always pre-fix or suffix the column name to make it non-reserved. Sometimes, if I just cannot logically prefix or suffix a column name, I just use "the". Like "TheGroup". But more often than not you can come up with a logical prefix or suffix, like "ProductGroup" or "AuthorGroup" (use the noun representing the group to prefix it.). – Floorwalker

@Bavardage Unfortunately, "just call it something that's not a reserved word" is easier said than done. There are more than a thousand keywords and reserved words used by various database systems. (Yes, I’ve counted!) – Oak

Answer 4

I've been having this problem with SQL generated by JPA... Turned out I was using a variable name called limit.

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE EXPENSE_LIMIT (ID BIGINT NOT NULL, LIMIT[*] DECIMAL(19,2), ACCOUNT_ID BIGINT, EXPENSE_CATEGORY_ID BIGINT, PERIOD_ID BIGINT, PRIMARY KEY (ID)) "; expected "identifier"; SQL statement:

Where my model class had a field called limit.

The fix is to specify column name as

@Column(name = "`limit`")

Overturn

value was my Achilles heel – Irrefragable