Changing H2 mode from Oracle to PostgreSQL results in different values for TIMESTAMP and NUMBER?

[penguinEnum]

Currently working on a project to migrate a Java service from Oracle to PostgreSQL 11.

Changing the compatibility mode argument in the JDBC connection string to PostgreSQL resulted in only a few broken tests.

jdbc:h2:./target/runtime-db;AUTO_SERVER=TRUE;CACHE_SIZE=131072;MVCC=true;mode=PostgreSQL

These tests were only for things like the following:

NUMBER - Prices which are stored as `number(6,2)`

assertThat(result.getBasicPrice()).isEqualTo(expected.getBasicPrice());

Expecting: 1500
Result: 1500.[00]

TIMESTAMP - Created at, updated at values

Expecting:
  <2011-05-01T23:00>
to have same year, month and day as:
  <2011-05-02T00:00>

I have not changed any of the business logic or Java code.

From my understanding the timestamp in Oracle and Postgres both have the timezones defined.

For decimal number, I am not sure if its H2 or PostgreSQL that is adding on the trailing zeroes for number fields, can someone confirm this please?

I will be testing with a real PostgreSQL DB too and will compare, but any insight would be welcomed.

Thanks

[Evgenij Ryazanov]

Hello.

It looks like you use some outdated version of H2, because MVCC setting doesn't exist in recent releases.

PostgreSQL doesn't have the NUMBER data type, and this type is not a native type of H2 too, why you use it? It is from Oracle, H2 accepts it, but only for compatibility with Oracle. It will not work in the real PostgreSQL. You need to use NUMERIC(6, 2) instead and yes, trailing zeroes should be here. Behavior of Oracle is incorrect.

TIMESTAMP data type doesn't have a time zone. In H2 it is a local timestamp (in Oracle possibly too, but I don't remember it well).

TIMESTAMP in PostgreSQL is an local, but UTC-based timestamp. Its local value will be changed if time zone of the session will be changed.

There is a TIMESTAMP WITH TIME ZONE data type for absolute timestamps. Oracle and H2 preserve the time zone, PostgreSQL, unfortunately, doesn't preserve it and it returns timestamp in local time zone (but at the same absolute point in UTC).