Possible bug with generated ids and postgresql

[Victor Noël]

Hi,

I am not sure it is a bug or a mistake on my side, but I think it was working in the past. I am now using jOOQ 3.13.0.

I have the following code:

                Table<Record> table = DSL.table(DSL.name("stuffs"));
                Field<Integer> idField = DSL.field("id", SQLDataType.INTEGER);
                Field<String> nameField = DSL.field("name", SQLDataType.VARCHAR);
                tx.createTableIfNotExists(table)
                    .column(idField, SQLDataType.INTEGER.identity(true))
                    .column(nameField, SQLDataType.VARCHAR.nullable(false))
                    .constraint(DSL.constraint("pk_" + table.getName()).primaryKey(idField))
                    .execute();

And I get the following error when it is executed:

org.jooq.exception.DataAccessException: SQL [create table if not exists "stuffs"(id int generated by default as identity not null, name varchar not null, constraint "pk_table" primary key (id))]; ERROR: syntax error at or near "generated"
  Position: 58
    at org.jooq_3.13.0.POSTGRES.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2751)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:385)
    ...

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "generated"
  Position: 58
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2510)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2245)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:311)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:148)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:453)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:371)
    ... 16 common frames omitted

[Lukas Eder]

Hi Victor,

Starting from jOOQ 3.13, we've added support for PostgreSQL (and standard SQL) identity columns with https://github.com/jOOQ/jOOQ/issues/6659, which has been supported by PostgreSQL 10+.

To restore the old behaviour of generating the vendor specific serial4 types, please use the relevant versioned PostgreSQL dialect for your PostgreSQL version. Versioned dialects with legacy database feature support are available from the jOOQ Professional Edition and jOOQ Enterprise Edition.

Alternatively, a quick fix would be to use an ExecuteListener that patches the generated SQL string by regex-replacing the "generated by default as identity" expression by "serial4".

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/fc65a3c4-9a9d-42f3-bd30-8a93d1b4bd2a%40googlegroups.com.

[Victor Noël]

Yes, this is very clear. Indeed my database is still in version 9.

Thank you for the explanation, I will most certainly update my database as a consequence :)

Cheers

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

[Lukas Eder]

Good choice! You'll get a ton of great PostgreSQL goodies for free :)

Thanks,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/e74a7671-b812-4c44-9ec6-97ad59c3135f%40googlegroups.com.