Skip site navigation (1) Skip section navigation (2)

Re: Cross-datatype Comparisons and Indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: "Thomas F(dot)O'Connell" <tfo(at)sitening(dot)com>,pgsql-general(at)postgresql(dot)org
Subject: Re: Cross-datatype Comparisons and Indexes
Date: 2004-08-20 21:09:01
Message-ID: 2541.1093036141@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Joshua D. DrakeDate: 2004-08-20 21:27:59
Subject: Re: Cross-datatype Comparisons and Indexes
Previous:From: Bruno Wolff IIIDate: 2004-08-20 21:00:07
Subject: Re: Cross-datatype Comparisons and Indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group