Bug on migration occurred after updating a field with an enum for type PostgreSQL 9.4

[Ddam]

Hi, I use this bundle and I think I found a bug

I use symfony 2.7 with DoctrineEnumBundle.
When modifying an existing field in a table to add an enum type, migration that is produced for PostgreSQL 9.4 is invalid.

Migration obtained looks like this:

ALTER TABLE table ALTER field TYPE VARCHAR(255) CHECK(field IN (\'value\', \'value\', \'value\'))

But this SQL statement is invalid, it returns the following error after starting the migration

SQLSTATE[42601]: Syntax error: 7 ERREUR:  erreur de syntaxe sur ou près de « CHECK »

I think this is a bug.
Thank you.

[fre5h]

OK. I'll check it later. Thanks

[jaimz22]

this happened to me the other day with Symfony 3 and Postgres 9.4 as well. it also was an alter table statement

[yceruto]

I have the same problem, any news?

[jaimz22]

they way I fixed this for myself was to delete the existing table then reconstruct the whole thing. Fortunately for me, I was in the position where I was able to do this

[timbrd]

If you want to keep your existing data, you can rewrite the migration to:

    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('CREATE TYPE custom_type AS ENUM(\'A\', \'B\', \'C\')');
        $this->addSql('ALTER TABLE my_table ALTER COLUMN enum_col DROP DEFAULT');
        $this->addSql('ALTER TABLE my_table ALTER COLUMN enum_col TYPE custom_type USING enum_col::text::custom_type ');
    }

[yceruto]

@timbordemann thanks, but now throw this exception when doctrine:migration:diff is executed

[Doctrine\DBAL\DBALException]
Unknown database type custom_type requested, Doctrine\DBAL\Platforms\PostgreSQL92Platform may not support it.

any suggestions?

[yceruto]

Solved \o/

# Doctrine Configuration
doctrine:
    dbal:
        #...
        mapping_types:
            custom_type: string # <=======

[SCIF]

@fre5h , seems like that is a bug cause comment does not solve issue.

Also experienced:

 [Doctrine\DBAL\DBALException]                                                                                                 
  Unknown database type listing_ownershiptype_enum requested, Doctrine\DBAL\Platforms\PostgreSQL92Platform may not support it.

migration:

$this->addSql("CREATE TYPE listing_stage_enum AS ENUM ('online', 'staging', 'relisting', 'completed');");
        $this->addSql('CREATE TABLE listing (
…
            stage listing_stage_enum DEFAULT \'online\' NOT NULL,
…
$this->addSql('COMMENT ON COLUMN listing.stage IS \'(DC2Type:ListingStage)\'');

entity.php:

    /**
     * @var string
     *
     * @ORM\Column(name="stage", type="ListingStage", length=32)
     */
    private $stage;

config.yml:

doctrine:
    dbal:
        types:
            ListingStage: AppBundle\Entity\Enums\ListingStage

Only adding

doctrine:
    dbal:
        mapping_types:
            listing_stage_enum: string

as of above solved issue.

[fre5h]

@SCIF In the first code snippet you wrote listing_ownershiptype_enum but in next snippets listing_stage_enum. Did I miss something?

This SQL $this->addSql("CREATE TYPE listing_stage_enum AS ENUM ('online', 'staging', 'relisting', 'completed');"); you created manually? Because bundle cannot create two different SQL statements which are related to one field. Only one definition for field is allowed.

So if you create a table first time and it has enum field then SQL statement for PostgreSQL is generated by this code

if ($platform instanceof PostgreSqlPlatform || $platform instanceof SQLServerPlatform) {
    return sprintf('VARCHAR(255) CHECK(%s IN (%s))', $fieldDeclaration['name'], $values);
}

Bundle cannot at first create this CREATE TYPE listing_stage_enum AS ENUM...
and then use CREATE TABLE listing (stage listing_stage_enum... Because it are two different statements. There is no hook in Doctrine to associate separate SQL statement with some field. That is why only field definition syntax is allowed ALTER TABLE table ALTER field TYPE VARCHAR(255) CHECK(field IN (\'value\', \'value\', \'value\'))
If you use your custom syntax, that no guarantee that bundle and Doctrine will support it.

[SCIF]

Hey @fre5h!

I cite a migration code just to make you understand which structure of db i created. I mixed a enums one with other. Just replace listing_ownershiptype_enum with listing_stage_enum in my message. Yes, doctrine generated another but i read your workaround and adopted it to postgres.

[innophilia-thomas]

this error still occurs for symfony 6.1 / postgres 14.2.
I used a workaround:

1. Drop the column entirely
2. migrate
3. recreate it as enum
4. migrate