Re: Cross-datatype Comparisons and Indexes

Bruno Wolff III <bruno@wolff.to> writes:
>   "Thomas F.O'Connell" <tfo@sitening.com> wrote:
>> Since the current stable version of postgres (7.4.x) doesn't allow
>> cross-datatype comparisons of indexes, is it always necessary to cast
>> my application data explicitly in order for an index to be used, even
>> among the integer types?

> Yes.

I can think of at least three workarounds in 7.4:

1. Always quote your constants:

    ... WHERE bigintcol = '42';

2. Use a prepared statement:

    PREPARE foo(bigint) AS ... WHERE bigintcol = $1;

    EXECUTE foo(42);

3. Use parameterized statements in extended-query mode (essentially the
   same idea as #2, but at the protocol level).  This doesn't help for
   pure SQL scripts, but is very workable when coding against libpq or
   JDBC.  Among other things it gets you out of worrying about SQL
   injection attacks when your parameter values come from untrusted
   sources.

            regards, tom lane