Batch Insert bind behavior differs from single insert

[m.la...@gmail.com]

Hi,

I'm trying to write a batch insert statement using jOOQ. I have two versions, one that inserts a single piece of data, and one that does a batch insert with a list of data. The code inserting a single item works, and the code doing the batch insert doesn't. I'm trying to use the PostGIS ST_GeomFromGeoJSON function to insert geojson into a Postgres database.

create.transaction(configuration -> {
    DSL.using(configuration).insertInto(TABLE_NAME, TABLE.COL1, TABLE.COL2, TABLE.GEOM, TABLE.COL4)
        .values(
            data.getCol1(),
            data.getCol2(),
            DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", new ObjectMapper().writeValueAsString(data.getGeom()))),
            data.getCol4())
        .execute()
});

This code creates a log line:

19:12:44.218 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : insert into "public"."table_name" ("col1", "col2", "geom", "col4") values (?, cast(? as timestamp), ST_GeomFromGeoJSON(?), ?::jsonb)
19:12:44.219 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind values      : insert into "public"."table_name" ("col1", "col2", "geom", "col4") values (13, timestamp 'timestamp', ST_GeomFromGeoJSON('{"type":"Point","coordinates":[5.0,10.0]}'), 'random json'::jsonb)

Trying to do this with a list of data elements I have:

create.transaction(configuration -> {
    BatchBindStep bindStep = DSL.using(configuration).batch(
        DSL.using(configuration).insertInto(TABLE_NAME, TABLE.COL1, TABLE.COL2, TABLE.GEOM, TABLE.COL4
        ).values((Integer) null, null, null, null)
    );
    for (Data d : dataList) {
        bindStep.bind(
            d.getCol1(),
            d.getCol2(),
            DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", new ObjectMapper().writeValueAsString(data.getGeom()))),
            d.getCol4()
        );
    }
    try {
        bindStep.execute();
    } catch (DataAccessException ex) {
        // Log ex, and getNextException
    }
});

This yields an error:

9:13:54.933 [main] ERROR t.b.p.s.Service - Error while executing SQL.

org.jooq.exception.DataAccessException: SQL [insert into "public"."table_name" ("col1", "col2", "geom", "col4") values (?, cast(? as timestamp), cast(? as varchar), cast(? as varchar)::jsonb)]; Batch entry 0 insert into "public"."table_name" ("col1", "col2", "geom", "col4") values ('14', cast('timestamp' as timestamp), cast('{"type":"Point","coordinates":[0.0,0.0]}' as varchar), cast('random json' as varchar)::jsonb) was aborted.  Call getNextException to see the cause.

Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."table_name" ("col1", "col2", "geom", "col4") values ('14', cast('timestamp' as timestamp), cast('{"type":"Point","coordinates":[0.0,0.0]}' as varchar), cast('random json' as varchar)::jsonb) was aborted.  Call getNextException to see the cause.

19:13:54.935 [main] ERROR t.b.p.s.Service - Next exception: 

org.postgresql.util.PSQLException: ERROR: column "geom" is of type geometry but expression is of type character varying

  Hint: You will need to rewrite or cast the expression.

When I have the statement batched, jOOQ doesn't seem to be inserting the SQL to use ST_GeomFromGeoJSON. I don't understand why. Help would be greatly appreciated!

[Lukas Eder]

Does your code work if instead of writing

DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", new ObjectMapper().writeValueAsString(data.getGeom())))

You write

DSL.field(DSL.sql("ST_GeomFromGeoJSON(?::text)", new ObjectMapper().writeValueAsString(data.getGeom())))

?

--
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.
For more options, visit https://groups.google.com/d/optout.

[m.la...@gmail.com]

It does not. I played around with it a little bit more and discovered that it does work if I use the following to create the BatchBindStep.

BatchBindStep bindStep = DSL.using(configuration).batch(
    DSL.using(configuration).insertInto(TABLE_NAME, TABLE.COL1, TABLE.COL2, TABLE.GEOM, TABLE.

COL4
    ).values((Integer) null, null, DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", "")), null)
);