Database Error - convert boolean to integer

hi yii-developers,

i have problem with storing a boolean field of a model in my postgresql-db (vers. 8.3). Example extract from table:

CREATE TABLE tbl_user(

  id integer NOT NULL DEFAULT nextval('tbl_user_id_seq'::regclass),

  /* ...  */

  agb boolean,

  CONSTRAINT tbl_user_pkey PRIMARY KEY (id )


If I want to update this field I use the following SQL-statement:

UPDATE "tbl_user" SET "agb"=true WHERE "tbl_user"."id"=6

If I using yii, it should worked with follow code:

class BenutzerController extends Controller{ //...

    public function actionUpdate($id){

   	$model = $this->loadModel( 6 );

   	$model->agb = true;

   	$model->save( false, array('agb') );



On purpose, I set the agb value to true and save it without validation. The SQL statement executed was:

UPDATE "tbl_benutzer" SET "agb"=:yp0 WHERE "tbl_benutzer"."id"=6. Bound with :yp0=true

But than the error:

CDbCommand couldn't execute SQL-Statement: SQLSTATE[42804]:  Datatype mismatch: 7 ERROR:  

column "agb" is of type boolean but  expression is of type integer

LINE 1: 

UPDATE "tbl_user" SET "agb"=1 WHERE "tbl_user"."id"=6

The boolean that I set, suddenly is an integer with 1. Maybe someone has the same problem and could help me to understand or fix this problem.


  • PostgreSQL 8.3 - [/i]Server Version 8.3.17

[i]- Yii 1.1.7

  • Server software: Apache/2.2.17 (Win32) PHP/5.3.14

[/i]thx & greetz badi[i]


PHP and postgres has different ways of representing boolean values.

By postgres documentation -

Try to use

$model->agb = 'true';

Best is to change the datatype from Boolean to Int.

it is not best to change datatype.

you can use yii Active Record type to update data as shown above by mdomba.

or in your query use…

UPDATE tbl_user SET agb= 'true' WHERE id= '6'

Thank you for your quick help…

Here my results:

@mdomba: I followed your link and tried it in all variations, but nothing works.

@Kiran Sharma: I’m with you, thats not in my opinion to change this datatype - cause of logic.

I found out that the way I tried this example works perfect under PHP version 5.3.6 but at my new laptop recently I installed the PHP version 5.3.14. And in this environment it doesn’t work.

Maybe it’s a bug in PHP or with the newer version I should change some configs or change something in the yii framework. For now I go back to the previos version, it works also fine at my server.

Perhaps someone has a workaround for this situation

greetz badi

thats gr8 as system works,

and as i say it is not best to change datatype, but you can change at initial level if you found difficulties.

you can also use bit or smallint datatype supported by postgresql.

I am having the same problem. I am running:

Yii 1.10

PHP 5.4.4

PostgreSQL 9.1.4

Somewhere along the line the boolean value is converted to an integer.

I fixed this by removing the ‘emulatePrepare’ => true, in the ‘db’ component of config/main.php. I did this after reading PHP Bug #62593 Emulate prepares behave strangely with PARAM_BOOL.

Thank you! This saved my day :)

Ticket is in ‘assigned’ status… hope they’ll fix it soon…