Phinx: Postgresql - seeding boolean column gets: Invalid text representation for false value

[sergioalcaraz]

Hi, I get this error when run seeder:

Error: [PDOException] SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: ""

Under:

• PHP 7.0.13
• Phinx version 0.6.5
• CakePHP 3.3.15
• Postgresql 9.5

The error only occurs when the value is false
['in_offer' => false]

Migrations for the column is:

$table->addColumn('in_offer', 'boolean', [
    'default' => false,
    'null' => false,
]);

SQL from pgadmin

ALTER TABLE public.products ADD COLUMN in_offer boolean;
ALTER TABLE public.products ALTER COLUMN in_offer SET NOT NULL;
ALTER TABLE public.products ALTER COLUMN in_offer SET DEFAULT false;

[mariorez]

I get the same error running the seeder command in MySQL adapter:

[PDOException (HY000)]                                                                                
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'is_user_admin' at row 1

The error only occurs when the value is false.

Exception trace:
 () at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:392
 PDOStatement->execute() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:392
 Phinx\Db\Adapter\PdoAdapter->insert() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Db/Table.php:651
 Phinx\Db\Table->saveData() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Db/Table.php:639
 Phinx\Db\Table->update() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Db/Table.php:675
 Phinx\Db\Table->save() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Seed/AbstractSeed.php:197
 Phinx\Seed\AbstractSeed->insert() at /my-app-path/db/seeds/Seeder003UserCompanyGroup.php:43
 Seeder003UserCompanyGroup->run() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php:154
 Phinx\Migration\Manager\Environment->executeSeed() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php:395
 Phinx\Migration\Manager->executeSeed() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php:514
 Phinx\Migration\Manager->seed() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Console/Command/SeedRun.php:110
 Phinx\Console\Command\SeedRun->execute() at /my-app-path/vendor/symfony/symfony/src/Symfony/Component/Console/Command/Command.php:265
 Symfony\Component\Console\Command\Command->run() at /my-app-path/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:826
 Symfony\Component\Console\Application->doRunCommand() at /my-app-path/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:189
 Symfony\Component\Console\Application->doRun() at /my-app-path/vendor/robmorgan/phinx/src/Phinx/Console/PhinxApplication.php:83
 Phinx\Console\PhinxApplication->doRun() at /my-app-path/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:120
 Symfony\Component\Console\Application->run() at /my-app-path/vendor/robmorgan/phinx/bin/phinx:28

[ThomasNucleus]

Still an issue, just happened to me now

EDIT: workaround for now is to make the boolean values in your seeder into strings

Eg do
'fieldname' => "false",
instead of
'fieldname' => false

[Furqanaa]

I had the same issue too

[dereuromark]

Are you able to provide a patch with your suggested changes as PR?

[gabrielrtakeda]

I think this also occurs in mysql..

Step #2 - "migrate": START TRANSACTION
Step #2 - "migrate": PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'ativo' at row 1 in /workspace/php/vendor/cakephp/database/Statement/MysqlStatement.php:39

a workaround for this is use 0 for false and 1 for true

public function up()
{
    $builder = $this->getQueryBuilder();
    $builder
        ->update('cidades')
-       ->set('ativo', false)
+       ->set('ativo', 0)
        ->where(function ($exp) {
            return $exp
                ->isNull('jusbrasil_tid');
        })
        ->execute();
}

public function down()
{
    $builder = $this->getQueryBuilder();
    $builder
        ->update('cidades')
-       ->set('ativo', true)
+       ->set('ativo', 1)
        ->execute();
}