Parameters are being sent as strings (quoted) and it can alter query behavior

[c2h5oh]

CREATE TABLE tests (
    id bigint,
    name text
);

INSERT INTO tests (id, name) VALUES (1, "first"), (2, "second"), (3, "third");

This works fine:

// get results in order: id=3, id=2, id =1
db.Query("SELECT * FROM tests WHERE id IN ( $1, $2, $3 ) ORDER BY CASE WHEN id = $4 THEN $5 WHEN id = $6 THEN $7 WHEN $8 THEN $9 END;",
   1, 2, 3, // IDs
    3, 0, // id 3, order 0
    2, 1, // id 2, order 1
    1, 2, // id 1, order 2
)
// result:
// 3, 'third'
// 2, 'second'
// 1, 'first'

But this doesn't:

// get results in order: id=3, id=1, id =2
db.Query("SELECT * FROM tests WHERE id IN ( $1, $2, $3 ) ORDER BY CASE WHEN id = $4 THEN $5 WHEN id = $6 THEN $7 WHEN $8 THEN $9 END;",
   1, 2, 3, // IDs
    3, 0, // id 3, order 0
    2, 10, // id 2, order 10
    1, 2, // id 1, order 2
)
// result:
// 3, 'third'
// 2, 'second'
// 1, 'first'

// expected:
// 3, 'third'
// 1, 'first'
// 2, 'second'

If order is not passed as parameters it works again

// get results in order: id=3, id=1, id =2
db.Query("SELECT * FROM tests WHERE id IN ( $1, $2, $3 ) ORDER BY CASE WHEN id = $4 THEN 0 WHEN id = $6 THEN 10 WHEN $8 THEN 2 END;",
   1, 2, 3, // IDs
    3, // id 3, order 0
    2, // id 2, order 10
    1, // id 1, order 2
)
// result:
// 3, 'third'
// 1, 'first'
// 2, 'second'

Same is true if I explicitely cast parameters to a numeric type

// get results in order: id=3, id=1, id =2
db.Query("SELECT * FROM tests WHERE id IN ( $1, $2, $3 ) ORDER BY CASE WHEN id = $4 THEN $5::bigint WHEN id = $6 THEN $7::bigint WHEN $8 THEN $9::bigint END;",
   1, 2, 3, // IDs
    3, 0, // id 3, order 0
    2, 10, // id 2, order 10
    1, 2, // id 1, order 2
)
// result:
// 3, 'third'
// 1, 'first'
// 2, 'second'

The reason for this is that in Postgres 0 < 2 < 10 but '0' < '10' < '2' - strings of variable length only compare chars up to the end of shorter string (and if it's a tie compare length)

[cbandy]

This driver sends arguments in the "text" format without implying anything about the Go type.¹ PostgreSQL is responsible for inferring the types of query parameters and casting the untyped arguments.

In this case, it's not possible for PostgreSQL to infer that the constants you place after THEN are intended to be integers. The solution, as you've found, is to give PostgreSQL more context with which to infer the types correctly. You can simplify a little bit by casting only once outside the CASE:

... ORDER BY (CASE ... END)::bigint;

or

... ORDER BY CAST(CASE ... END AS bigint);

¹ See a related (same?) discussion here.