Framework: [5.2] exception "(jsonb) type may not support" when calling the getColumnType() on the pgsql database.

[boogiebug]

I was trying to get the column type of a 'json/jsonb' column in a table on pgsql 9.4 server with the following code and then, Laravel throw an exception when I tried to get the column type.

    $schema = $this->getConnection()->getSchemaBuilder();
    $table  = $this->getTable();
    foreach ( $schema->getColumnListing($table) as $column ) {
      $type = $schema->getColumnType($table, $column);
    }

Doctrine\DBAL\DBALException: Unknown database type jsonb requested, Doctrine\DBAL\Platforms\PostgreSqlPlatform may not support it.

Is this a bug?

Please, also anyone suggest the work-around to detect the 'json/jsonb' column type as well.

Thanks!

[EliasZ]

Mapping matrix says that for pgsql >= 9.4 JSONB is already supported.

This issue on doctrine/dbal suggests that it is fixed in 2.5. You are probably using an older version?

This doesn't seem to be Laravel related.

[boogiebug]

So Laravel need to update it's Doctrine, right? Since, I found this issue on Laravel 5.2 and seem that Doctrine is being used by Laravel?

[EliasZ]

doctrine/dbal is an optional package required for some database operations which you have manually added in the past to your project its composer.json file. I have no idea which version of doctrine/dbal you are using in your application. Laravel suggests installing ~2.4, try using ~2.5 instead.

[boogiebug]

Thanks for your suggestion.

Unfortunately, I cannot find the doctrine/dbal entry in my composer.json. Instead, it is in composer.lock and it's version is 2.5.4

I decided to query it directly from the pgsql. Thanks for your help, anyway!

[alexmantaut]

I know this is an old issue, but for anyone with the same problem; change the composer/dbal version to dev (for jsonb support) just execute:

composer require doctrine/dbal:@dev

[JeroenvdV]

I'm getting this error when running the following migration:

        Schema::table('documents', function(Blueprint $table){
            $table->renameColumn('name', 'unique_file_name');
        });

I changed Doctrine:

  - Removing doctrine/dbal (v2.5.4)
  - Installing doctrine/dbal (dev-master ec8fc16)
    Cloning ec8fc16604915110eea8046e3ea594c133aee880

But the error persists.

For completeness, this is the table before the migration:

wobotek=# \d+ documents
                                      Table "public.documents"
    Column     |              Type              | Modifiers | Storage  | Stats target | Description 
---------------+--------------------------------+-----------+----------+--------------+-------------
 guid          | uuid                           | not null  | plain    |              | 
 company_guid  | uuid                           | not null  | plain    |              | 
 name          | character varying(255)         | not null  | extended |              | 
 current_state | character varying(255)         | not null  | extended |              | 
 vhe_number    | character varying(255)         | not null  | extended |              | 
 created_at    | timestamp(0) without time zone |           | plain    |              | 
 updated_at    | timestamp(0) without time zone |           | plain    |              | 
 synced_at     | timestamp(0) without time zone |           | plain    |              | 
 extra_data    | jsonb                          |           | extended |              | 
 deleted_at    | timestamp(0) without time zone |           | plain    |              | 
 district_guid | uuid                           |           | plain    |              | 
 user_guid     | uuid                           |           | plain    |              | 

I worked around this by changing the migration to:

        DB::statement("ALTER TABLE documents RENAME name TO unique_file_name");

See also https://github.com/laravel/framework/pull/14359

[chipk4]

@JeroenvdV in documentation for v.5.2 you can see "Note: Modifying any column in a table that also has a column of type enum, json or jsonb is not currently supported." So, you can't modify column through Laravel. Use DB::statement it's a good idea.

[vollossy]

@chipk4 Most interesting that even if you trying to change non-json column, error still persists

Schema::table('units', function (Blueprint $table) {
    $table->integer('site_id');
    $table->foreign('site_id')->references('id')->on('sites');
    $table->integer('controller_id')->nullable()->change();
});

[JeroenvdV]

That is what it says, yes. It took me a while to figure it out so I posted here, I didn't see that snippet in the docs so I'm not sure if it wasn't added later.