Thread: Cross-datatype Comparisons and Indexes

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?

E.g., If I have a table with a bigint primary key and application data
compared against that primary key, must I always explicitly cast the
application data to bigint if I want postgres to use the index?

SELECT * FROM some_table WHERE primary_key_column = 42

vs.

SELECT * FROM some_table WHERE primary_key_column = 42::int8

There's no way to tell postgres to treat incoming data implicitly as
the type of the column, right?

Thanks!

-tfo

On Fri, Aug 20, 2004 at 16:41:40 -0400,
  "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.

This will work better in 8.0.

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

>
>
> I can think of at least three workarounds in 7.4:
>
> 1. Always quote your constants:
>
>     ... WHERE bigintcol = '42';

You can also

      WHERE bigintcol = 42::bigint

Sincerely,

Joshua D. Drake



>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Thomas F.O'Connell 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?
>
> E.g., If I have a table with a bigint primary key and application
> data compared against that primary key, must I always explicitly
> cast the application data to bigint if I want postgres to use the
> index?
>
> SELECT * FROM some_table WHERE primary_key_column = 42
>
> vs.
>
> SELECT * FROM some_table WHERE primary_key_column = 42::int8
>
> There's no way to tell postgres to treat incoming data implicitly
> as the type of the column, right?

You can always set your initial sequence values to > 4.2 billion
(2^32) which could also probably expose some 4-byte integer
assumptions your code may make. You'd not need to use an explicit
cast in that scenario either.

Mike Mascari