Cross-datatype Comparisons and Indexes

[Thomas F.O'Connell]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruno Wolff III]

On Fri, Aug 20, 2004 at 16:41:40 -0400,
"Thomas F.O'Connell" <tf*@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Bruno Wolff III <br***@wolff.to> writes:

"Thomas F.O'Connell" <tf*@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Joshua D. Drake]

>

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.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mike Mascari]

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match