Re: Cross-datatype Comparisons and Indexes

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> "Thomas F.O'Connell" <tfo(at)sitening(dot)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