Create table error in 2.x, unsure how to interpret: expected "IDENTITY, BIGSERIAL, SERIAL, data type"

[Vladimir Dzhuvinov]

Hi all,

We decided to bite the bullet and start a migration towards the new 2.x :)

The online docs / diagrams - are they now up to date with the 2.x SQL?

We are having difficulty interpreting this message:

Syntax error in SQL statement "CREATE TABLE IF NOT EXISTS subject_sessions (id VARCHAR(500) NOT NULL, tid VARCHAR(50) NOT NULL, sub VARCHAR(500) NOT NULL, auth_ts TIMESTAMP NOT NULL, acr VARCHAR(500), amr ARRAY[*], creation_ts TIMESTAMP NOT NULL, access_ts TIMESTAMP NOT NULL, max_life BIGINT DEFAULT -1, auth_life BIGINT DEFAULT -1, max_idle BIGINT DEFAULT -1, claims VARCHAR(10000), rps ARRAY, data VARCHAR(10000), PRIMARY KEY (id, tid))"; expected "IDENTITY, BIGSERIAL, SERIAL, data type";

This is from a create table statement used with 1.4.200.

The intent is to have a primary key over the "id" and "tid" columns, of type VARCHAR.

Cheers,

[Vladimir Dzhuvinov]

Hi again,

After some trial and error I figured out the ARRAYs in H2 need to be typed now in v2.x, defined e.g. as "VARCHAR(100) ARRAY ".

Perhaps I should file a ticket to consider updating the syntax error message to help other devs.

Cheers,

[Andrei Tokar]

Hi Vladimir,

Please take a look at your error message again:
... amr ARRAY[*], creation_ts ...
Marker [*] shows where parser had choked, and something of sort "IDENTITY, BIGSERIAL, SERIAL, data type" is expected instead.

At this point, it should be pretty clear that ARRAY has to be typed, and that is indeed a new requirement in 2.x, in line with SQL standard.

You can consult documentation for exact syntax: https://h2database.com/html/datatypes.html#array_type

And yes, syntax diagrams are supposed to be up to date.

[Vladimir Dzhuvinov]

Thanks Andrei. I wasn't aware the "[*]" had that significance.

I posted a suggestion in the H2 website git repo to consider indicating the applicable H2 version on the top of the ref pages.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/KOE7A1de1p0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/fbc0af26-5d0b-41f2-9681-5df89ba0e514n%40googlegroups.com.

--

Vladimir Dzhuvinov :: vlad...@dzhuvinov.com