Keary Suska <hierophant@xxxxxxxxxx> writes: > This thread has particular interest to me as well--the libpq documentation > and examples seem to imply that the backend will consider all parameters to > be string literals, although postgres is smart enough to perform > conversions. No, not a "string literal" exactly --- simple quoted literals are initially taken as being of *unknown* type, as are parameter symbols that haven't had a specific type assigned. While it's true that the parser will often assign unknown literals type "text" if it has no other way to infer a type, that's a last resort. Commonly a unknown literal gets resolved as being the same type as whatever it is compared to or otherwise first used with. Here are a couple of examples: regression=# select 42 = '42'; ?column? ---------- t (1 row) regression=# select 42 = 'foo'; ERROR: invalid input syntax for integer: "foo" regression=# select 42::numeric = 'foo'; ERROR: invalid input syntax for type numeric: "foo" regression=# select 42 = '42.0'; ERROR: invalid input syntax for integer: "42.0" The parser would similarly have assumed that the unknown literal was integer or numeric if the operator were, say, + rather than =. The contents of the string are not examined at all until the type decision is made, which is why 'foo' and '42' are treated the same. > However, there was an issue (perhaps no longer in 8.x) that in > cases of implicit conversions the planner would have the tendency to perform > sequential searches when otherwise (i.e. when the type is more explicit) it > would do an index search. Implicit conversions never had anything to do with it. The weak spot of the index system is that *cross type* comparisons were unindexable until recently. So if you wrote something like WHERE id = 42 this would be indexable if id was int4 (like the constant) but not if it was int8 or numeric. That problem actually goes away if you use quoted literals or parameters: WHERE id = '42' WHERE id = $1 In either case, the literal or parameter will be resolved as being the same type as "id", and so you don't risk creating an unindexable comparison. So at least for examples like this, it's *better* not to specify a type for the parameter. As of 8.0, btree indexes can handle cross-type comparisons, at least for most of the common cases, so this isn't as big a deal as it once was. But it's still true that you should usually avoid specifying parameter types unless you have to. "Have to" means you get an error like this: regression=# select - '42'; ERROR: operator is not unique: - "unknown" HINT: Could not choose a best candidate operator. You may need to add explicit type casts. regression=# Here the parser is punting because it hasn't any good way to guess which numeric type to impute to the literal. regards, tom lane
