Thread: implicit casting problem

Hi,

We have an app which uses the latest version of perl DBI and DBD::Pg to
execute a query, using placeholders. It fails to cast the float I send
into a number and generates the following message

"DBD::Pg::st execute failed: ERROR:  invalid input syntax for
integer: "2.63" at -e line 8."

Here is a little test case:

tom@tux6204:~ $ perl -MDBI -e '
my $dbh = DBI->connect( "DBI:Pg:dbname=prod;host=pgdb",
                        "compl", "devsql",
                        {RaiseError => 1,
                         AutoCommit => 0,
                         FetchHashKeyName => "NAME_lc"});
my $sth = $dbh->prepare("update tmp set a = a *?");
$sth->execute(2.63);
'
DBD::Pg::st execute failed: ERROR:  invalid input syntax for
integer: "2.63" at -e line 8.

-------------------------- end test case

Here is me rooting around on the psql command line trying to work out what
is happening.

Welcome to psql 7.4.5, the PostgreSQL interactive terminal.
...
prod=> create table tmp (a int);
CREATE TABLE
prod=> insert into tmp values (2);
INSERT 2392267569 1
prod=> update tmp set a = a * 2.63;
UPDATE 1
prod=> update tmp set a = a * '2.63';
ERROR:  invalid input syntax for integer: "2.63"
prod=> update tmp set a = (a::float * '2.63');
UPDATE 1
prod=> update tmp set a = a::float * '2.63';
UPDATE 1
prod=> update tmp set a = floort(a * '2.63');
ERROR:  invalid input syntax for integer: "2.63"
prod=> update tmp set a = floort(a * 2.63);
ERROR:  function floort(numeric) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.
prod=> update tmp set a = floor(a * 2.63);
UPDATE 1

I thought it was a problem with perl DBI, but then the fact "(a::float *
'2.63')" works suprises me.

Is this a problem with Postgres, or with perl's placeholder mechanism
quoting a number that doesn't need quoting.

Thanks,

Tom Larard

Fixed, this was a problem with weakly typed languages such as perl,
sometime it doesn't know what SQL_TYPE to send, so it sends
SQL_VARCHAR.

The solution is to use
bind_param(<placeholder_number>,<value>,DBI::SQL_FLOAT);

Tom

Tom Larard <larard@cs.umb.edu> writes:

Just for reference these two cases are interpreted in ways that may not be
intuitive for programmers. Most languages treat constants that look like 'foo'
as string constants and then have rules for how string constants get cast.
Postgres has string datatypes but 'foo' isn't necessarily a string data type.
It's of unknown type. It default to being cast to a string in some cases but
if it's used in arithmetic or function calls it can be cast to whatever's
necessary.

> prod=> update tmp set a = a * 2.63;
> UPDATE 1

In this case 2.63 is recognized by the parser as a numeric constant which is
an arbitrary precision data type. So postgres sees <integer> * <numeric> and
picks the appropriate * operator. That operator returns a numeric result which
it then casts to integer to handle the assignment.

So it parses as "set a = (a::numeric * 2.63)::integer"

> prod=> update tmp set a = a * '2.63';
> ERROR:  invalid input syntax for integer: "2.63"

In this case the parser sees '2.63' which is not a string, but rather a
constant of unknown type. In that case postgres sees <integer> * <unknown> and
picks the most convenient type for the unknown side. Usually (always? I'm not
sure) the choice is the same type as the other side of the operator. So it
then tries to cast the unknown to an integer and fails.

> prod=> update tmp set a = (a::float * '2.63');
> UPDATE 1

In this case postgers sees <float> * <unknown> and the same thing happens and
it works.

> prod=> update tmp set a = a::float * '2.63';
> UPDATE 1

<float> * <unknown>

> prod=> update tmp set a = floort(a * '2.63');
> ERROR:  invalid input syntax for integer: "2.63"

<integer> * <unknown>

> prod=> update tmp set a = floort(a * 2.63);
> ERROR:  function floort(numeric) does not exist
> HINT:  No function matches the given name and argument types. You may need
> to add explicit type casts.

So in this case it sees <integer> * <numeric> which it can satisfy with the
numeric*numeric operator which returns a numeric. But there's no
floort(numeric) operator.

> prod=> update tmp set a = floor(a * 2.63);
> UPDATE 1

This is "set a = (floor(a::numeric * <numeric>))::integer"

--
greg