Postgres 9.4 Add INDEX USING GIN

Question

I am using Postgres 9.4, JSONB fields, those are working good. But when I add GIN index doctrine migration fails to created index.

class Version20160314132147_Gin_Indexes_content   extends AbstractMigration {

public function up(Schema $schema) {
    $this->addSql('CREATE INDEX contents_properties_ix ON contents USING gin (properties);');
}

/**
 * @param Schema $schema
 */
public function down(Schema $schema) {
    $this->addSql('DROP INDEX contents_properties_ix;');
}
}

Error: [Doctrine\DBAL\Driver\PDOException] SQLSTATE[42704]: Undefined object: 7 ERROR: data type json has no default operator class for access method "gin" HINT: You must specify an operator class for the index or define a default operator class for the data type.

Composer.json { "slim/slim": "~2.6", "doctrine/orm": "~2.5", "doctrine/dbal": "2.5", "symfony/yaml": "2.8.@dev", "doctrine/cache": "~1.5@dev", "symfony/console": "2.8.@dev", "symfony/class-loader": "^2.7.4", "doctrine/migrations": "~1.0", "predis/predis": "~1.0.1", "guzzlehttp/guzzle": "~5.0", "twig/twig": "~1.0", "php-di/php-di": "^5.1.0", "monolog/monolog": "~1.15@dev", "boldtrn/jsonb-bundle": "~1.0", "opsway/doctrine-dbal-postgresql": "~0.1", "fzaninotto/faker": "~1.3.0", "aws/aws-sdk-php": "^3.9" }

Answer 1

Postgres seems to be whining about your column properties not being a text type and therefore not being able to index it using GIN.

You may have success casting the column to text:

CREATE INDEX contents_properties_ix ON contents USING gin (properties::text);