Hi Evgenij,
I just saw that Katzyn has already committed the correction
<https://github.com/h2database/h2database/pull/3568>.
Maybe you know where I can find the corrected H2 jar archive?
And maybe you can help me to finish the integration of auto increment in
Base?
Apparently H2 does not support a command like:
CREATE TABLE "PUBLIC"."PUBLIC"."Table1" ("ID" INTEGER NOT NULL GENERATED BY
DEFAULT AS IDENTITY, "Name" VARCHAR(100), PRIMARY KEY ("ID"))
If so, I can create the table then the index in two separate commands:
CREATE TABLE "PUBLIC"."PUBLIC"."Table1" ("ID" INTEGER NOT NULL GENERATED BY
DEFAULT AS IDENTITY, "Name" VARCHAR(100))
ALTER TABLE "PUBLIC"."PUBLIC"."Table1" ADD INDEX .....
I don't know the second command...
Thanks.
Le lundi 4 juillet 2022 à 04:06:33 UTC+2, Evgenij Ryazanov a écrit :
> Hello.
>
> Identity columns don't have any dedicated data types in the SQL Standard
> and it the most of database systems. When you declare an identity column
> you must specify both data type (BIGINT or INTEGER, for example) and
> identity clause (GENERATED BY DEFAULT AS IDENTITY etc.)
>
> Few database systems have special data types with implicit identity
> characteristics due to historic reasons. PostgreSQL has serial data types (
> smallserial = serial2, serial = serial4, bigserial = serial8). Normally
> identity columns should be used in modern versions of PostgreSQL instead of
> these legacy types, but at least JDBC drivers have something to report
> here. PgJDBC reports only smallserial, serial, and bigserial data types
> in getTypeInfo() with TRUE in AUTO_INCREMENT column.
>
> H2 doesn't have special types (compatibility modes have some) and returns
> FALSE for all data types.
>
> HSQLDB doesn't have special types, but it returns TRUE for TYNYINT,
> SMALLINT, INTGER, BIGINT, DECIMAL and NUMERIC. These data types may be
> used for identity columns, but an additional clause must be specified in
> their definitions.
>
> Derby doesn't have special types, it returns TRUE for SMALLINT, INTEGER,
> and BIGINT, so situation is the same. It also returns NULL for LOB data
> types, it looks like a bug.
>
> It means some drivers (PgJDBC, H2) assume that TRUE in this column
> indicates a special data type with implicit identity characteristics, some
> other drivers (HSQLDB, Derby) assume that TRUE indicates a data type that
> may be used for an identity column with additional identity generation
> clauses.
>
> Why this deviation exists? In the SQL Standard, INFORMATION_SCHEMA is
> described in a separate book with more than 300 pages and we usually can
> say where it was implemented correctly and where it isn't correct. But JDBC
> metadata is only described in its own Javadoc and these descriptions are
> too brief and obscure. In many cases it isn't possible to determine what
> they exactly mean, so developers of JDBC drivers need to guess or they can
> try to copy behavior of some other driver.
>
> I think we can change our implementation to return TRUE for TINYINT,
> SMALLINT, INTEGER, BIGINT, and NUMERIC data types.
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/6373be3a-7a47-4462-9de2-ec05c3c80003n%40googlegroups.com.
