Gorm ERROR: operator does not exist: date >= integer (SQLSTATE 42883)

GORM Playground Link

https://github.com/go-gorm/playground/pull/463

Description

On PostgreSQL 14.2, was comparing date in a WHERE clause, but get:

ERROR: operator does not exist: date >= integer (SQLSTATE 42883)

The comparison should work since date(now()) - 0 returns a date type (via psql), but somehow in gorm, it evaluates as an integer type.

Comment From: a631807682

Use clause.Expr or specify the type of ?

    err := DB.
        Model(&User{}).
        Select("max(id)").
        Where("date(created_at) >= date(now()) - ?", clause.Expr{SQL: strconv.Itoa(0)}).
        Group("date(created_at)").
        Find(&max).Error

    err := DB.
        Model(&User{}).
        Select("max(id)").
        Where("date(created_at) >= date(now()) - ?::int", 0).
        Group("date(created_at)").
        Find(&max).Error

gorm sql log is not really executed sql, it will actually be converted to $1 $2 and create a prepared statement, that's why you fail. It is caused by gpx, I'm not sure if it's a bug.

// fail
PREPARE fooplan  AS
    SELECT max(id) FROM "users" WHERE date(created_at) >= date(now()) - $1 AND "users"."deleted_at" IS NULL GROUP BY date(created_at);

https://www.postgresql.org/docs/9.3/sql-prepare.html

Comment From: raphlcx

Thanks @a631807682, those are very useful pointers.