Where with parametrized INTERVAL

[ikawalec]

Hi,

I'm having an issue with using where expr with parametrized INTERVAL.

Code:

d := 3

builder := r.StatementBuilderType.
  Delete("test").
  Where(squirrel.Expr("expiry <= now()::timestamptz - INTERVAL '? DAYS'", d))

query, args, err = builder.ToSql()

logrus.Infof("query: %s, args: %+v", query, args)

// db execute

I'm getting the following error:

ERROR: could not parse "$1 DAYS" as type interval: interval: missing number at position 0: "$1 DAYS" (SQLSTATE 22007)

Generated query and args:

query: DELETE FROM test WHERE expiry \u003c= now()::timestamptz - INTERVAL '$1 DAYS'
args: [3]

Expected SQL:

delete from test where expiry <= now()::timestamptz - INTERVAL '3 days';

Is this a bug? Is there any other way this can be achieved?

Thanks!

[lann]

Assuming you are using postgres, I believe the quotes are the problem: INTERVAL '? DAYS' -> INTERVAL ? DAYS

[ikawalec]

Assuming you are using postgres, I believe the quotes are the problem: INTERVAL '? DAYS' -> INTERVAL ? DAYS

That's correct, I'm using postgres. Without quotes, it's not a valid SQL statement:

delete from test where expiry <= now()::timestamptz - INTERVAL 3 days;

at or near "3": syntax error
DETAIL:  source SQL:
delete from test where expiry <= now()::timestamptz - INTERVAL 3 days

[lann]

It looks like postgres expects the number to be a string, so you may also need to e.g. strconv.Itoa the Go int before passing it to the database.

[ikawalec]

Thanks but still even with days converted usingstrconv.Itoa I still get the same error (assuming quotes are mandatory).

squirrel.Expr("expiry <= now()::timestamptz - INTERVAL '? DAYS'", strconv.Itoa(days)),

ERROR: could not parse "$1 DAYS" as type interval: interval: missing number at position 0: "$1 DAYS" (SQLSTATE 22007)

[lann]

The quotes mean you are passing the literal string "? DAYS"; Squirrel converts the ? to $1 because its placeholder replacement doesn't know how to deal with strings.

I believe what you need is:

squirrel.Expr("expiry <= now()::timestamptz - INTERVAL ? DAYS", strconv.Itoa(days)),

[ikawalec]

The quotes mean you are passing the literal string "? DAYS"; Squirrel converts the ? to $1 because its placeholder replacement doesn't know how to deal with strings.

I believe what you need is:

squirrel.Expr("expiry <= now()::timestamptz - INTERVAL ? DAYS", strconv.Itoa(days)),

The example that you provided will generate the following SQL query:

DELETE FROM test WHERE expiry \u003c= now()::timestamptz - INTERVAL 3 DAYS

In order for that to work with postgres: https://www.postgresql.org/docs/8.0/functions-datetime.html, the INTERVAL should use quotes '3 DAYS':

DELETE FROM test WHERE expiry \u003c= now()::timestamptz - INTERVAL '3 DAYS'

[lann]

Ah sorry, I got bad information from Google. In that case I would expect something like this to work:

squirrel.Expr("expiry <= now()::timestamptz - INTERVAL ?", fmt.Sprintf("%d DAYS", days))

[ikawalec]

Ah sorry, I got bad information from Google. In that case I would expect something like this to work:

squirrel.Expr("expiry <= now()::timestamptz - INTERVAL ?", fmt.Sprintf("%d DAYS", days))

Unfortunately, this would also fail:

 query: DELETE FROM test WHERE expiry \u003c= now()::timestamptz - INTERVAL $1
 args: [3 DAYS]
 error: ERROR: at or near "1": syntax error (SQLSTATE 42601)