H2 vs PostgreSQL generated column with function

Question

I'm trying to setup a generated column which will also take null checks into consideration when subtracting values. In PostgreSQL I did:

ALTER TABLE session ADD COLUMN
    duration INTERVAL GENERATED ALWAYS AS age(time_ended, time_started) STORED;

H2 doesn't support age function so I another patch to create alias to function:

CREATE ALIAS age FOR "net.agileb.config.H2Functions.age";

and corresponding java code:

package net.agileb.config;

import java.time.Duration;
import java.time.LocalDateTime;

public class H2Functions {
    public static Duration age(LocalDateTime endDate, LocalDateTime startDate) {
        return Duration.between(endDate, startDate);
    }
}

I run H2 in PostgreSQL compatibility mode:

    public:
      type: com.zaxxer.hikari.HikariDataSource
      url: jdbc:h2:mem:agileb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL;
      driverClassName: org.h2.Driver

but h2 still doesn't like the syntax of the generated column:

SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "ALTER TABLE SESSION ADD COLUMN
    DURATION INTERVAL GENERATED[*] ALWAYS AS AGE(TIME_ENDED, TIME_STARTED) STORED"; expected "YEAR, MONTH, DAY, HOUR, MINUTE, SECOND"; SQL statement:
ALTER TABLE session ADD COLUMN
    duration INTERVAL GENERATED ALWAYS AS age(time_ended, time_started) STORED [42001-200]
Location   : db/migration/V1606395529__topic_calculated_duration_column.sql (/home/agilob/Projects/springowy/build/resources/main/db/migration/V1606395529__topic_calculated_duration_column.sql)
Line       : 3
Statement  : ALTER TABLE session ADD COLUMN
    duration INTERVAL GENERATED ALWAYS AS age(time_ended, time_started) STORED

I understand H2 wants me to use specific interval like INTERVAL SECOND, generated as identity and STORED keyword doesn't seem to be supported.

Is there a way to make this query work in PostgreSQL and H2?

score 2 · Accepted Answer · answered Nov 27 '20 at 11:46

There is no way to use the same syntax for generated columns in PostgreSQL and H2.

INTERVAL data type without interval qualifier is a feature of PostgreSQL. Other DBMS, including the H2, support only standard-compliant intervals such as INTERVAL YEAR, INTERVAL YEAR(3) TO MONTH, INTERVAL DAY TO SECOND, etc. Hopefully, you can use standard-compliant interval data types in PostgreSQL too, they are also supported. But all these types a either year-month intervals or daytime intervals. Interval with YEAR and/or MONTH fields can't have DAY, HOUR, MINUTE, or SECOND fields and vice versa. If you really need a mixed interval with all these fields, you can use only the PostgreSQL and its forks.
H2 1.4.200 supports only non-standard syntax for generated columns with AS keyword (upcoming H2 2.0 also supports the standard syntax GENERATED ALWAYS AS). PostgreSQL doesn't support non-standard syntax from H2. You can build H2 from its current sources to have the possibility to use the same standard syntax here.
The biggest problem is that PostgreSQL requires non-standard STORED clause at the end of definition of generated column for a some weird reason and doesn't accept standard-compliant definitions of columns. H2 and others don't have and don't accept this clause.

So the only solution here is to use different SQL for PostgreSQL and for H2.

Thanks, I had no idea it was postgres that was non-standard here, but also H2 being non standard with `AS`. I think I will store the duration just as `int` in seconds in that case. I would rather avoid having different SQL patch for the two databases, would have to write more java specific code for flyway. — agilob, Nov 27 '20 at 13:50

H2 vs PostgreSQL generated column with function

1 Answers1