jOOQ fetched timestamp has no nanoseconds

Question

In a postgres db, using jOOQ, when I fetch a row that has a column defined as

timestamp without time zone

when I do a select and I get the value (fetched by jOOQ into a java.sql.Timestamp), then I see that the nanoseconds are missing.

E.g., in the database I have:

2016-04-04 15:14:10.970048

but jOOQ returns a Timestamp with value

2016-04-04 15:14:10.0

This is a problem for further comparisons. How can I prevent this?

---

UPDATE Upon request, I'll provide more details.

In Postgresql I have a type:

CREATE TYPE schema.my_type AS
(
  mt_page                  smallint,
  mt_active_from   timestamp without time zone,
);

I call a function, using the routines:

DSLContext dsl = ....

MyTypeRecord [] records = Routines.myRoutine(dsl.configuration); 

Then, the Timestamp will not have no nanos

The function is:

CREATE OR REPLACE FUNCTION shop.myRoutine(
  OUT my_types schema.my_type[]
  )
  RETURNS schema.my_type[] AS
$BODY$
DECLARE
BEGIN
  BEGIN

          SELECT ARRAY(
            SELECT
                  ROW(a_id, a_timestamp)::schema.my_type
            FROM schema.article
          ) INTO my_types;
  END;
  RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 1000;

Answer 1

This is a bug in jOOQ: https://github.com/jOOQ/jOOQ/issues/5193

jOOQ internally implements a composite type deserialisation algorithm, as PostgreSQL's JDBC driver, unfortunately, doesn't implement SQLData and related API for out-of-the-box composite type support. The current implementation (jOOQ 3.7.3) parses timestamps without their fractional seconds part.

As a workaround, you could implement your own data type binding to work around this issue.