Better Support for Postgres Interval #393
Description
Is your feature request related to a problem? Please describe.
- When your schema uses postgres interval, Jet seems does not support doing an upsert (on conflict ) in postgres.
For example.
insertStmt := funcapSchema.HtFleetStatus.
INSERT(funcapSchema.HtFleetStatus.AllColumns).
MODEL(req).
ON_CONFLICT(funcapSchema.HtFleetStatus.RouterName).
DO_UPDATE(SET(
funcapSchema.HtFleetStatus.LastModified.SET(funcapSchema.HtFleetStatus.EXCLUDED.LastModified),
funcapSchema.HtFleetStatus.UptimeValue.SET(funcapSchema.HtFleetStatus.EXCLUDED.UptimeValue),
),
)
When UptimeValue is an interval, the SET command is not exposed, so it cannot be used as part of an upsert operation.
- There should be no reason not to read in time.Interval as a time.Duration. It's peculiar to have it read in as a string and then rely on the user to convert a very postgres specific format to a time stamp.
Describe the solution you'd like
I'd like to have SET be available to pg interval and have the default data type be mapped to time.Duration.
Work Around
I'll post this in here for anyone who runs into this as well.
- Code Generator
if column.DataType.Name == "interval" {
defaultTableModelField.Type = template.NewType(new(time.Duration))
}- Upsert
insertStmt := RawStatement(`
insert into funcap.ht_fleet_status (router_name, uptime_value, last_modified) values
($router_name, $interval::interval, now()) on conflict (router_name) do update
set uptime_value = excluded.uptime_value, last_modified = now() `,
RawArgs{
"$router_name": router,
"$interval": duration,
},
)- Query
stmt := SELECT(funcapSchema.HtFleetStatus.RouterName,
//Raw required to allow use time.Duration in go model
RawInt("(extract(epoch from ht_fleet_status.uptime_value)* 1000000000)::bigint").AS("ht_fleet_status.uptime_value"),
funcapSchema.HtFleetStatus.LastModified,
).FROM(funcapSchema.HtFleetStatus).WHERE(funcapSchema.HtFleetStatus.RouterName.EQ(String(routerName)))