How do I bind a float to a SQL command in Yii for Postgres?

Question

I get the error

Database Exception – yii\db\Exception

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "0.0001"
The SQL being executed was: select ad_id, sum(clicks) sc, sum(impressions) si, from ad_table group by keyword_id 
having sum(clicks)/sum(impressions) < 0.0001 
Error Info: Array
(
    [0] => 22P02
    [1] => 7
    [2] => ERROR:  invalid input syntax for integer: "0.0001"
)
↵
Caused by: PDOException

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "0.0001"

My code is

$sql = ...
  ."having sum(clicks)/sum(impressions) < :ctr "; 
$ids = Yii::$app->db->createCommand($sql)
  ->bindValue(':ctr', (float)getConfigValue('ctr'))
  ->queryColumn();

I tried setting the 3rd parameter to \PDO::PARAM_STR, but it still gave the same error. I also tried removing the (float) cast, but same error. There is not even a decimal type available: http://php.net/manual/en/pdo.constants.php

This worked in MySQL. I'm migrating to Postgres. The SQL works directly in psql.

Reference: http://www.yiiframework.com/doc-2.0/yii-db-command.html#bindValue()-detail

Which SQL works directly in PostgreSQL? Also please add database table structure with column types. And why you are not using ActiveQuery and ActiveRecord? Or yii\db\Query at least? — arogachev
– arogachev, Commented Sep 23, 2015 at 4:21
@CraigRinger There is no column. It is a parameter to a having clause. @ arogachev The SQL presented in the error message works directly in Postgres. I just copy & paste, and it works. The table structure is irrelevant as it's not a column. I didn't use ActiveRecord because I only needed a column, there are hundreds of thousands of records, and the SQL was too complicated to figure out with ActiveRecord. — Chloe
– Chloe, Commented Sep 23, 2015 at 5:05
@arogachev Ahh, I found another place with an error: ->having(['<', 'c', 20000]), so even though I used Query, I still had to change the code to ->having(['<', 'count(*)', 20000]). — Chloe
– Chloe, Commented Sep 23, 2015 at 20:31

greg · Accepted Answer · 2015-09-23 09:40:57Z

1

In Postgres, dividing two integers together is an integer division hence you are comparing an integer with a float.

select 2/3; -- returns 0
select 2/3.0; -- returns 0,6666…7

Try having sum(clicks)/cast(sum(impressions) as float4) < 0.0001

answered Sep 23, 2015 at 9:40

greg

3,5351 gold badge27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

greg Over a year ago

yup, having integer division is pretty useful in some cases :)

greg Over a year ago

Probably because the value you were comparing with was not a parameter, so the type hinting process was not done the same way.

Collectives™ on Stack Overflow

How do I bind a float to a SQL command in Yii for Postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related