Postgres: pg type for `time.Time` is TIMESTAMPTZ without option of changing it

[Link512]

Hi,

I'm working with a table that has a timestamp without time zone field. When performing a bulk update using db.NewValues, the CTE generated has the field as timestamp with time zone, which breaks further queries in my application logic.

To reproduce:

type User struct {
	bun.BaseModel `bun:"users,alias:u"`

	Value int `bun:"val"`

	TimeField time.Time `bun:"tf"`
}

func main() {
	dsn := "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
	driver := pgdriver.NewConnector(pgdriver.WithDSN(dsn))
	sqldb := sql.OpenDB(driver)
	pg := bun.NewDB(sqldb, pgdialect.New(), bun.WithDiscardUnknownColumns())
	pg.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))

	_, err := pg.Exec("CREATE TABLE IF NOT EXISTS users(val integer, tf timestamp without time zone);")
	if err != nil {
		panic(err)
	}

	users := []*User{
		{
			Value:     1,
			TimeField: time.Now(),
		},
		{
			Value:     2,
			TimeField: time.Now(),
		},
	}
	_, err = pg.NewUpdate().
		With("_data", pg.NewValues(&users)).
		Model((*User)(nil)).
		TableExpr("_data").
		Set("tf = _data.tf").
		Where("u.val = _data.val").
		Exec(context.Background())

}

The statement produced is

WITH "_data" ("val", "tf") AS (VALUES (1::BIGINT, '2022-07-28 19:27:21.576234+00:00'::TIMESTAMPTZ), (2::BIGINT, '2022-07-28 19:27:21.576234+00:00'::TIMESTAMPTZ)) UPDATE "users" AS "u" SET tf = _data.tf FROM _data WHERE (u.val = _data.val)

Is there a posibillity of adding a type:timestamp struct tag for time.Time fields that will make them a timestamp instead of timestamptz?

[vmihailenco]

See https://bun.uptrace.dev/postgres/postgres-data-types.html#timestamptz-vs-timestamp. You can override the type with bun:"type:timestamp".