Better Support for Postgres Interval

[safaci2000]

Is your feature request related to a problem? Please describe.

1. 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.

2. 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.

1. Code Generator

	if column.DataType.Name == "interval" {
		defaultTableModelField.Type = template.NewType(new(time.Duration))
	}

2. 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,
		},
	)

3. 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)))

[go-jet]

Indeed, SET method is missing on ColumnInterval type.

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.

time.Duration can't be used as scan destination for postgres interval column since database usually returns interval in textual format. Also time.Duration doesn't have Scan method. pgtype.Interval can be used as interval destination.

RawInt("(extract(epoch from ht_fleet_status.uptime_value)* 1000000000)::bigint").AS("ht_fleet_status.uptime_value"),

It doesn't have to be raw string:

CAST(EXTRACT(EPOCH, HtFleetStatus.UptimeValue).MUL(Int(1000000000))).AS_BIGINT().AS("ht_fleet_status.uptime_value")

[safaci2000]

Indeed, SET method is missing on ColumnInterval type.

So, to confirm, the SET missing is a missing feature not an intentional choice, correct?

time.Duration can't be used as scan destination for postgres interval column since database usually returns interval in textual format. Also time.Duration doesn't have Scan method. pgtype.Interval can be used as interval destination.

Okay, that's fair. When you say pgtype, you're referring to the pgx driver data type, correct?

It doesn't have to be raw string:

CAST(EXTRACT(EPOCH, HtFleetStatus.UptimeValue).MUL(Int(1000000000))).AS_BIGINT().AS("ht_fleet_status.uptime_value")

Thank you for the code snippet I love when I can do things in Jet as much as I can.

[go-jet]

So, to confirm, the SET missing is a missing feature not an intentional choice, correct?

Yeah, it is a bug.

Okay, that's fair. When you say pgtype, you're referring to the pgx driver data type, correct?

Yes, this one - https://github.com/jackc/pgtype