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