Re: Basic questions about PQprepare() - Mailing list pgsql-general

Keary Suska <hierophant@pcisys.net> 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