如何使用pgx控制已准备好的SQL语句中的参数类型？

Question

I'm using jackc/pgx for postgresql-10 connections in a web application. Prepared statement syntax is slightly different than the database/sql standard library, but the issue remains the same.

Prepared statement and execution:

_, err := conn.Prepare("listContacts", `
    select id, name, phonenumber, email from contacts
        order by name
        limit $1
        offset $2
`)
....
//ParseUint caches invalid negative input, returns uint64
page, err := strconv.ParseUint(r.FormValue("page"), 10, 8)
if err != nil {
    http.Error(w, "Invalid argument", http.StatusBadRequest)
    return
}
limit := 27
offset := (page - 1) * l //Forgot parentheses here, caused Error
rows, err := pool.Query("listContacts", limit, offset)
if err != nil {
    http.Error(w, "Internal server error", http.StatusInternalServerError)
    return
}
....

This parameters should and appears to be of an integer type. However, due to an error my code calculated offset to a gigantic value. This resulted in a query error: 18446744073709551591 is greater than maximum value for Int8. I rectified the original error.

However, it does make me wonder... Why Int8? And how to have some control over this decision? According postgresql documentation for prepared statements this can be set in a parameter:

...A corresponding list of parameter data types can optionally be specified. When a parameter's data type is not specified or is declared as unknown, the type is inferred from the context in which the parameter is used (if possible)....

Since the table doesn't have too many entries, I guess postgresql choose Int8.

data_type

The data type of a parameter to the prepared statement. If the data type of a particular parameter is unspecified or is specified as unknown, it will be inferred from the context in which the parameter is used. To refer to the parameters in the prepared statement itself, use $1, $2, etc.

I can't find the data_type option in jackc/pgx or database/sql documentation.

I see the problem as follows:

• I use strconv.ParseUint to convert and sanitize user input to a positive-only value.
• I could modify the code to use strconv.ParseInt, do the multiplication and test if the resulting value is positive fits in int8. Reply with Http.StatusBadRequest if it doesn't
• If the database will grow in production I will have to update my code!?
• Or allow for a bigger values with int32 or int64 and throw nasty Http.StatusInternalServerError to the client when the query fails

But, the solution I'm really looking for: tell postgresql to expect Uint64 and deal with the empty result set towards the client in a fashionable way.

Answer 1

Why Int8?

error: 18446744073709551591 is greater than maximum value for Int8

---

PostgreSQL tries to find an integer type that is large enough to represent the constant value 18446744073709551591. The largest integer type PostgreSQL has is int8 (8 bytes or 64 bits) or bigint, which has a maximum value of 9223372036854775807. The error message says 18446744073709551591 is greater than 9223372036854775807, which is correct.

Reference: Chapter 8. Data Types, PostgreSQL 10

Name    Aliases     Description
bigint  int8        signed eight-byte integer

---

I use strconv.ParseUint to convert and sanitize user input to a positive-only value.

---

That is a bad idea. Use strconv.ParseInt, check that input value is within the valid range, and write correct code.