postgresql - 如何在 go 中使用 postgres 间隔？

Question

我有一个连接到 postgres 数据库的 go 项目。我想在一定天数内删除订单 - 我试过这样做：

type SQLOrderDatabase struct {
    Connection *pgx.Conn
    logger     *logrus.Entry
}



interval := pgtype.Interval{}

_ = interval.Set(time.Second * 5)
_, err := database.Connection.Exec(ctx, "DELETE FROM store.items WHERE store.time > (now() AT TIME ZONE 'utc' - $1);", &interval)

这是使用 pgx 库和 pgx 类型。但是，它始终失败并出现此错误：

Error:ERROR: operator does not exist: timestamp without time zone \u003e interval (SQLSTATE 42883)","severity":"error","

反正有没有使用postgres间隔？

标签： postgresqlgopgx

Answer 1

There isn't enough context for postgres to know how you want to subtract the data passed as $1 so you need to explicitly tell it, which you can do by type casting the parameter.

(now() AT TIME ZONE 'utc' - $1::interval)