Boolean type with PostgreSQL

Simple Postgres table:




CREATE TABLE public.test (

  id INTEGER NOT NULL, 

  val BOOLEAN NOT NULL, 

  CONSTRAINT test_pkey PRIMARY KEY(id)

);



Do this:




Yii::app()->db->createCommand()->insert('test', array(

		'id'=>1,

		'val'=>true,

	));



Everithing’s allright:

But doing this




Yii::app()->db->createCommand()->insert('test', array(

		'id'=>1,

		'val'=>false,

	));



I’m getting the error:

Am I wrong?

seems to be a bug.

please report it on github: https://github.com/yiisoft/yii/issues

and post a link to the issue here.

Found this is a semi-bug/semi-feature in PHP+PDO:

https://bugs.php.net/bug.php?id=33876

Here is the solution to fix it in the Yii:

Report an issue:

https://github.com/yiisoft/yii/issues/779

Hi, I’m facing the same problem actually with my fixture and boolean type in tables… I read your post and pasted the code you wrote in cdbCommand in order to avoid problem but it still appears.

To be honest, I don’t know why but in cdbCommand in “execute()” function, the $params array is always empty… Though the error return is well “boolean waited but type integer given”…

Where do I put :


if($params) {  

  foreach($params as $param=>$value) 

  {                     

    $this->bindValue($param, $value);                 

  }

}

$this->_statement->execute();

is it in execute() function in cdbCommand ?

$params is always empty don’t understand why

EDIT :

Ok I solved the problem of casting boolean in integer with postgres adding in CdbCommandBuilder:208




          if($column->dbType==='boolean') //

            $values[self::PARAM_PREFIX.$i]=$value;//

          else//

            $values[self::PARAM_PREFIX.$i]=$column->typecast($value);



Hi all,

I had the same problem with the following setup:

Ubuntu 12.04 + postgres 9.2 + PHP 5.4 + Yii 1.1.

I tried first the fix suggested from Zugluk:

adding in CdbCommandBuilder:208 (i.e. createInsertCommand function)




          if($column->dbType==='boolean') //

            $values[self::PARAM_PREFIX.$i]=$value;//

          else//

            $values[self::PARAM_PREFIX.$i]=$column->typecast($value);



It works when inserting a new entry in the table but

updates remained a problem.

I also tried to copy past the same code snippet in the

createUpdateCommand but with no results.

After some hours digging around Yii code I was able to

track back to another function: CDbColumnSchema::typecast($value)

CDbColumnSchema is a base class which is extended by database-dependent ones.

In the case of postresql the extended class is called CDbPgsqlColumnSchema.

I fixed the problem as follows:

Copied the function typecast($value) from the base class CDbColumnSchema.

Pasted it in the class CDbPgsqlColumnSchema.

Finally I added a control on the data type at the beginning of the freshly

copied function.

The final code (that is the one that you have to insert in the CDbPgsqlColumnSchema class)

is the following




        /**

	 * Converts the input value to the type that this column is of.

	 * @param mixed $value input value

	 * @return mixed converted value

	 */

	public function typecast($value)

	{

		/*WARNING: I had to add this check:

		 * when the table column is a boolean there are problems because php 

		 * represents boolean differently and there is a misconversion

		 * with this modification it seems to work

		 */

		if(stripos($this->type,'bool')!==false){

			if($value===TRUE || $value===true || $value === 't' || $value === 'true' ||

					$value==='y' || $value==='yes' || $value==='on' ||

					$value==='1' || $value===1 ) 

				return 'true';

			

			else 

				return 'false';

		}

		//END OF MODIFICATION

			

		if(gettype($value)===$this->type || $value===null || $value instanceof CDbExpression)

			return $value;

		if($value==='')

			return $this->type==='string' ? '' : null;

		switch($this->type)

		{

			case 'string': return (string)$value;

			case 'integer': return (integer)$value;

			case 'boolean': return (boolean)$value;

			case 'double':

			default: return $value;

		}

		

	}



With this code in place everything seems to work (inserts as well as updates).

You can remove the patch provided by Zugluck as well.

cheers

My code had to be applied for all times you found “$values[self::PARAM_PREFIX.$i]=$column->typecast($value);”, It seems to me it appears 3 times in the file. ;)

I only found two occurrences of "$values[self::PARAM_PREFIX.$i]=$column->typecast($value);".

This is probably why it was only working partly :)

I should have missed the third occurrence

However, I fell on same problem although my changes…

I have one model, but two different forms in order to edit fields separately. In rules I defined, according to the scenario, the safe and unsafe fields. And when I apply a form, the false boolean fields which can be editable only with the second form are set to ‘void’ (which is normal for in database false is set to ‘void’) giving the same problem :(

has someone tested with 1.1.13 ??

EDIT

OK !! completing tremity suggestion I got it working in all cases :




  public function typecast($value)

  {

    /*WARNING: I had to add this check:

     * when the table column is a boolean there are problems because php 

     * represents boolean differently and there is a misconversion

     * with this modification it seems to work

     */

    if(stripos($this->type,'bool')!==false ||

       stripos($this->type,'boolean')!==false){

      if($value===TRUE || $value===true || $value === 't' || $value === 'true' ||

          $value==='y' || $value==='yes' || $value==='on' ||

          $value==='1' || $value===1 ) 

        return 'true';

      

      else 

        return 'false';

    }

    //END OF MODIFICATION

            

    if(gettype($value)===$this->type || $value===null || $value instanceof CDbExpression)

      return $value;

    if($value==='')

    { //

      if($this->type === 'bool') //KLUDGE If it's a boolean, when there is void we return '0'

        return '0'; //

      else //

        return $this->type==='string' ? '' : null;

    } //

      

    switch($this->type)

    {

      case 'string': return (string)$value;

      case 'integer': return (integer)$value;

      case 'boolean': return (boolean)$value;

      case 'double':

      default: return $value;

    }

          

  }

}

Hi all again (after a very long time). Just a couple of more little things.

So in the class CPgsqlColumnSchema add the code:




	public function typecast($value)

	{

		/*WARNING: I had to add this check:

		 * when the table column is a boolean there are problems because php 

		 * represents boolean differently and there is a misconversion

		 * with this modification it seems to work

		 */

		if(stripos($this->type,'bool')!==false || 

		   stripos($this->type,'boolean')!==false){

		   

		   if(is_string($value)) $lower_value = strtolower($value);

			

			if($value===TRUE || $value===true || $lower_value === 't' || $lower_value === 'true' ||

			   $lower_value==='y' || $lower_value==='yes' || $lower_value==='on' ||

			   $lower_value==='1' || $value===1 ) 

			return 'true';


			else 

			return 'false';

		

		}

		//END OF MODIFICATION

		

		if(gettype($value)===$this->type || $value===null || 

		   $value instanceof CDbExpression)

		  return $value;

		if($value===''){ //

			if($this->type === 'bool') //KLUDGE If it's a boolean, when there is void we return '0'

				return '0'; //

			else //

				return $this->type==='string' ? '' : null;

		} //

  

		switch($this->type){

		  case 'string': return (string)$value;

		  case 'integer': return (integer)$value;

		  case 'boolean': return (boolean)$value;

		  case 'double':

		  default: return $value;

		}

	}



Note that there is a small modification with respect to the code posted by Zugluk. The change is:




if(is_string($value)) $lower_value = strtolower($value);


if($value===TRUE || $value===true || $lower_value === 't' || $lower_value === 'true' ||

			   $lower_value==='y' || $lower_value==='yes' || $lower_value==='on' ||

			   $lower_value==='1' || $value===1 ) 

			return 'true';


			else 

			return 'false';



This is necessary for including any possible typing (e.g. TRUE, true, True, FALSE, False , and so on).

Moreover, it is necessary to modify the function createColumnCriteria in the class CDBCommandBuilder (at least in the version of the file I have) as follows:




	public function createColumnCriteria($table,$columns,$condition='',$params=array(),$prefix=null)

	{

		$this->ensureTable($table);

		$criteria=$this->createCriteria($condition,$params);

		if($criteria->alias!='')

			$prefix=$this->_schema->quoteTableName($criteria->alias).'.';

		$bindByPosition=isset($criteria->params[0]);

		$conditions=array();

		$values=array();

		$i=0;

		if($prefix===null)

			$prefix=$table->rawName.'.';

		foreach($columns as $name=>$value)

		{

			if(($column=$table->getColumn($name))!==null)

			{

				if(is_array($value))

					$conditions[]=$this->createInCondition($table,$name,$value,$prefix);

				else if($value!==null)

				{

					if($bindByPosition)

					{

						$conditions[]=$prefix.$column->rawName.'=?';

						$values[]=$value;

					}

					else

					{

						$conditions[]=$prefix.$column->rawName.'='.self::PARAM_PREFIX.$i;

						/* OLD 

						$values[self::PARAM_PREFIX.$i]=$column->typecast($value);

						*/

						$values[self::PARAM_PREFIX.$i]=$column->typecast($value);

						$i++;

					}

				}

				else

					$conditions[]=$prefix.$column->rawName.' IS NULL';

			}

			else

				throw new CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".',

					array('{table}'=>$table->name,'{column}'=>$name)));

		}

		$criteria->params=array_merge($values,$criteria->params);

		if(isset($conditions[0]))

		{

			if($criteria->condition!='')

				$criteria->condition=implode(' AND ',$conditions).' AND ('.$criteria->condition.')';

			else

				$criteria->condition=implode(' AND ',$conditions);

		}

		return $criteria;

	}



The change is




/* OLD 

$values[self::PARAM_PREFIX.$i]=$column->typecast($value);

*/

$values[self::PARAM_PREFIX.$i]=$column->typecast($value);



and it is necessary to make sure that active records use the typecast function defined above.

Cheers

I use PostgreSQL and the only issue I had is a well-known bug in PDO where you can’t bind boolean true and false values. You have to use 1 and 0.

There is a bug report in Yii repo on Github for this.

You solved my problem. thanks dude :)