Create Table Problem (Upgrading from 1.4.x)

[Peter Borissow]

Dear H2 Community,

    I have finally carved out some time to upgrade an old project to H2 2.x and ran into a problem with one of my table create statements:

CREATE TABLE USER_PREFERENCE (

    ID BIGSERIAL NOT NULL,
    KEY VARCHAR(50) NOT NULL,
    VALUE text NOT NULL,
    USER_ID bigint NOT NULL,
    CONSTRAINT PK_USER_PREFERENCE PRIMARY KEY (ID)

);

This statement is throwing a "Syntax error in SQL statement" error in 2.x that I did not see in 1.4.x.

In the error message there is something about "expected "identifier"; SQL statement".

If I take out or rename the "KEY" column everything works.

I see that the list of reserved keywords has been updated at some point and KEY has been added although it is marked as a non-reserved (NR) word in the SQL Standard:

http://www.h2database.com/html/advanced.html#keywords

Questions:

(1) If am running in PostgreSQL compatibility mode, and KEY is a non-reserved keyword in PostgreSQL shouldn't I be allowed to create a KEY column?

(2) Is this the correct way to set NON_KEYWORDS? Is it similar to setting mode?

java.util.Properties properties = new java.util.Properties();
properties.setProperty("MODE", "PostgreSQL");
properties.setProperty("NON_KEYWORDS", "KEY");

I ask because it doesn't seem to work (i.e. I still can't create my table with the NON_KEYWORDS set).

Thanks in advance,

Peter

[Evgenij Ryazanov]

Hello.

In modern versions of the SQL Standard KEY is really a non-reserved word, but it was a mistake to exclude it from the list of reserved words, because in some contexts you still cannot use it as identifier, even the SQL Standard itself has at least one such documented exclusion appeared in ISO/IEC 9075-2:2016 TECHNICAL CORRIGENDUM 2.

Definition of your table also has a VALUE column, but VALUE is a reserved word in the SQL Standard and it is a keyword in H2.

If you cannot rename these columns, you can add `;NON_KEYWORDS=KEY,VALUE` to JDBC URL. It also should have `;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH` if you want a better compatibility with PostgreSQL. Alternatively you can pass these settings in the map with properties.

[Peter Borissow]

Thank you Evgenij,

    Adding the following properties definitely helped:

properties.setProperty("MODE", "PostgreSQL");

properties.setProperty("DATABASE_TO_LOWER", "TRUE");
properties.setProperty("NON_KEYWORDS", "KEY,VALUE");
properties.setProperty("DEFAULT_NULL_ORDERING", "HIGH");

I am able to create a new database and load my schema :-)

I have a new issue in my code related to connection pooling but I'll save that for a new thread if I can't figure it out.

Thanks Again,

Peter

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit

https://groups.google.com/d/msgid/h2-database/7756da35-d074-439e-af77-33adc7a4bbd6n%40googlegroups.com

.