Postgresql 17 & jsonb error

[Erwane]

Hi, i'm trying to create migration with jsonb type but this result in an error

$this->table('issues')
    ->addColumn('permissions', AdapterInterface::PHINX_TYPE_JSONB)
    ->create();

psql log

2025-08-20T21:10:57.488030971Z 2025-08-20 21:10:57.487 UTC [38] ERROR:  syntax error at or near "NOT" at character 69
2025-08-20T21:10:57.488066611Z 2025-08-20 21:10:57.487 UTC [38] STATEMENT:  CREATE TABLE "public"."issues" ("id" SERIAL NOT NULL, "permissions" NOT NULL, CONSTRAINT "issues_pkey" PRIMARY KEY ("id"));

It's look like the field permissions has no type.

$this->table('issues')
    ->addColumn('permissions', AdapterInterface::PHINX_TYPE_JSON)
    ->create();

with PHINX_TYPE_JSON, it's ok and create ... a jsonb field ... :-/

vendor/bin/phinx --version
Phinx by CakePHP - https://phinx.org. 0.16.10

php -v
PHP 8.4.11 (cli) (built: Aug  3 2025 08:49:56) (NTS)
Copyright (c) The PHP Group
Built by Debian
Zend Engine v4.4.11, Copyright (c) Zend Technologies
    with Zend OPcache v8.4.11, Copyright (c), by Zend Technologies
    with Xdebug v3.4.5, Copyright (c) 2002-2025, by Derick Rethans

[MasterOdin]

I'm unable to reproduce the issue using the postgres adapter. I'm using the following test migration file:

<?php

declare(strict_types=1);

use Phinx\Db\Adapter\AdapterInterface;
use Phinx\Migration\AbstractMigration;

final class Test extends AbstractMigration
{
    public function change(): void
    {
        $this->table('issues')
            ->addColumn('permissions', AdapterInterface::PHINX_TYPE_JSONB)
            ->create();
    }
}

and it migrates successfully and the resulting table has jsonb:

$ PGPASSWORD=password psql -h 127.0.0.1 -p 5432 -U postgres -c '\d issues'
                              Table "public.issues"
   Column    |  Type   | Collation | Nullable |             Default
-------------+---------+-----------+----------+----------------------------------
 id          | integer |           | not null | generated by default as identity
 permissions | jsonb   |           |          |
Indexes:
    "issues_pkey" PRIMARY KEY, btree (id)

To my knowledge, there's been no real changes to that bit of the code for a long while, and it's surprising to hear it's not working.

---

The fact that the type is missing altogether from the generated SQL is very strange to me and would seem to indicate that AdapterInterface::PHINX_TYPE_JSONB is somehow blank for you, vs the expected value of jsonb.

Does putting the exact string 'jsonb' as the type work?

[MasterOdin]

@dereuromark is this also migrations related, similar to cakephp/phinx#2374?

[MasterOdin]

@Erwane are you using phinx directly, or via cakephp/migrations?

[markstory]

This is an issue in cakephp/migrations, not phinx.

[Mapiiik]

The same problem probably exists for the types "inet," "cidr," and "macaddr."

See:

            ->addColumn('mac_address', 'macaddr', [
                'default' => null,
                'limit' => null,
                'null' => true,
            ])

Generates:
"mac_address" DEFAULT NULL

Error:

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "DEFAULT"
LINE 1: ..._v4(), "name" VARCHAR DEFAULT NULL, "mac_address" DEFAULT NU...
                                                             ^
Query: CREATE TABLE "public"."radar_interferences" ("id" UUID NOT NULL DEFAULT uuid_generate_v4(), "name" VARCHAR DEFAULT NULL, "mac_address" DEFAULT NULL, "ssid" VARCHAR DEFAULT NULL, "signal" INT DEFAULT NULL, "radio_name" VARCHAR DEFAULT NULL, "created" TIMESTAMP(6) DEFAULT NULL, "modified" TIMESTAMP(6) DEFAULT NULL, CONSTRAINT "radar_interferences_pkey" PRIMARY KEY ("id"));

[Mapiiik]

I tried to work around this by using Literal. But in that case, it seems that support for it has been completely removed.

See:

            ->addColumn('mac_address', Literal::from('macaddr'), [
                'default' => null,
                'limit' => null,
                'null' => true,
            ])

Error:

Cannot access offset of type Migrations\Db\Literal in isset or empty
#0 /app/vendor/cakephp/migrations/src/Db/Adapter/PostgresAdapter.php(153): Cake\Database\Schema\PostgresSchemaDialect->columnDefinitionSql(Array)
#1 /app/vendor/cakephp/migrations/src/Db/Adapter/AdapterWrapper.php(317): Migrations\Db\Adapter\PostgresAdapter->createTable(Object(Migrations\Db\Table\Table), Array, Array)
#2 /app/vendor/cakephp/migrations/src/Db/Adapter/TimedOutputAdapter.php(112): Migrations\Db\Adapter\AdapterWrapper->createTable(Object(Migrations\Db\Table\Table), Array, Array)
#3 /app/vendor/cakephp/migrations/src/Db/Plan/Plan.php(147): Migrations\Db\Adapter\TimedOutputAdapter->createTable(Object(Migrations\Db\Table\Table), Array, Array)
#4 /app/vendor/cakephp/migrations/src/Db/Table.php(866): Migrations\Db\Plan\Plan->execute(Object(Migrations\Db\Adapter\TimedOutputAdapter))
#5 /app/vendor/cakephp/migrations/src/Db/Table.php(708): Migrations\Db\Table->executeActions(false)
#6 /app/config/Migrations/20200324085757_InitialNMS.php(440): Migrations\Db\Table->create()
#7 /app/vendor/cakephp/migrations/src/Migration/Environment.php(122): InitialNMS->up()
#8 /app/vendor/cakephp/migrations/src/Migration/Manager.php(449): Migrations\Migration\Environment->executeMigration(Object(InitialNMS), 'up', false)
#9 /app/vendor/cakephp/migrations/src/Migration/Manager.php(420): Migrations\Migration\Manager->executeMigration(Object(InitialNMS), 'up', false)
#10 /app/vendor/cakephp/migrations/src/Migration/BuiltinBackend.php(96): Migrations\Migration\Manager->migrate(20241113134406)
#11 /app/vendor/cakephp/migrations/src/Migrations.php(186): Migrations\Migration\BuiltinBackend->migrate(Array)
#12 /app/vendor/cakephp/migrations/src/TestSuite/Migrator.php(116): Migrations\Migrations->migrate(Array)
#13 /app/tests/bootstrap.php(79): Migrations\TestSuite\Migrator->runMany(Array)
#14 /app/vendor/phpunit/phpunit/src/TextUI/Application.php(337): include_once('/app/tests/boot...')
#15 /app/vendor/phpunit/phpunit/src/TextUI/Application.php(109): PHPUnit\TextUI\Application->loadBootstrapScript('/app/tests/boot...')
#16 /app/vendor/phpunit/phpunit/phpunit(104): PHPUnit\TextUI\Application->run(Array)
#17 /app/vendor/bin/phpunit(122): include('/app/vendor/php...')
#18 {main}

[Erwane]

@Erwane are you using phinx directly, or via cakephp/migrations?

With Cake migrations. Sounds like a plugin error like Mark and Matthew saids.
Thanks

[markstory]

@Mapiiik Yes, cakephp's database layer doesn't have support for macaddr and a few other column types right now. Support for those column types will be added in 5.3 though. Until then, you should either pin migrations to 4.6.* or use Migrations.backend = phinx.