Framework: Datatype mismatch using Eloquent Model

[exy]

• Laravel Version: 5.8.26
• PHP Version: 7.1.30
• Database Driver & Version:
  pgsql => 11.3

Database : Postgresql 10

Description:

When using (new Model())->save() on PostgreSQL database, the Illuminate\Database\Connection cast boolean to integer in the prepareBindings and bindValues methods.

Steps To Reproduce:

Create a table on MySql or Postgresql :

Schema::connection('postgresql')->create('test', function (Blueprint $table) {
      $table->bigIncrements('id');
      $table->boolean('column_boolean');
});

Create a Model :

use Illuminate\Database\Eloquent\Model;

class TestInsert extends Model
{
    protected $table = 'test';
    protected $primaryKey = 'id';
    protected $connection = 'postgresql';
}

Finally insert a new value :
$test = new TestInsert();
$test->save();

=> it should failed converting :
SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "column_boolean" is of type boolean but expression is of type integer

[mfn]

FTR in general PgSQLbooleans work fine/as expected for me. Laravel 5.8.26, PHP 7.2.19, PgSQL 9.6

In your example you are not setting any attributes?

Also your column_boolean is _not_ nullable(). So without setting any attribute, you should get an error; but a different one.

Is this example complete?

Can you show the actual statement generated, e.g. \DB::listen(function ($query) { \Log::debug($query->sql);});

[exy]

You are right, you need to add :
$table->boolean('boolean')->nullable(false)->default(false);

And set the attribute to true :
$test->column_boolean = true;

This little code cast to integer :

if ($value instanceof DateTimeInterface) {
     $bindings[$key] = $value->format($grammar->getDateFormat());
} elseif (is_bool($value)) {
     $bindings[$key] = (int) $value;
}

And you cannot insert an integer value in a boolean column type, it should be converted in string

[mfn]

Again, this totally works for me.

Is it an issue with newer Postgres _server_ version?

[exy]

I don't think it's an issue with newer Postgres server version, I've tried this on Postgres server 9.6 and 10 :

create table test
(
    id           integer               not null primary key,
    column_boolean          boolean default false not null
);
insert into test (id, column_boolean) values (1, 1);

Each time this error :

[42804] ERROR: column "column_boolean" is of type boolean but expression is of type integer
You will need to rewrite or cast the expression.

If I try with this (note that I insert string and not an integer) :
insert into test (id, column_boolean) values (2, '1');

It works

[staudenmeir]

Does DB::table('test')->insert(['id' => 1, 'column_boolean' => true]) work for you?

[exy]

Same :

SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "column_boolean" is of type boolean but expression is of type integer
LINE 1: insert into "test" ("column_boolean") values (1)
^
HINT: You will need to rewrite or cast the expression. (SQL: insert into "test" ("column_boolean") values (1))

[staudenmeir]

That's odd, it works for me on PostgreSQL 10.6 (in Homestead).

Please post the connection's settings in config/database.php.

[exy]

'driver' => 'pgsql',
'host' => 'db',
'port' => '5432',
'database' => 'postgres',
'username' => 'postgres',
'password' => 'toto',
'charset' => 'utf8',
'prefix' => '',
'options' => [
                PDO::ATTR_PERSISTENT => true,
                PDO::ATTR_EMULATE_PREPARES => true,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
],

[staudenmeir]

The error is caused by PDO::ATTR_EMULATE_PREPARES => true, Laravel uses false by default.

[exy]

ok that mean we can not let PDO prepare the statement with Postgres connection

[staudenmeir]

No, not with the current implementation. Do you need ATTR_EMULATE_PREPARES to be true ?

[ionutantohi]

Be aware that having ATTR_EMULATE_PREPARES=true causes other problems with MySQL on recent versions of php. See ( #23850, #27596, #26817, #25818).

[owengo]

@staudenmeir : with postgresql, we have to use a connection pooler, which allows sharing database sessions. This makes it impossible to use prepared statements, because this creates a temporary stored procedure for each statement.