implicit casting problem - Mailing list pgsql-general

From Tom Larard
Subject implicit casting problem
Date
Msg-id Pine.GSO.4.21.0411111242590.23065-100000@blade71.cs.umb.edu
Whole thread Raw
Responses Re: implicit casting problem
Re: implicit casting problem
List pgsql-general
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


pgsql-general by date:

Previous
From: Bjørn T Johansen
Date:
Subject: Problem with showing time diff?
Next
From: Bruno Wolff III
Date:
Subject: Re: OID Question