Invalid ALTER TABLE statement for Postgresql after enum update

[el-seirh]

Hi there

I have tested this bundle with an installation of a Postgresql 9.6 (current) database. In the \Fresh\DoctrineEnumBundle\DBAL\Types\AbstractEnumType::getSqlDeclaration() function the bundle has a special return format for Postgresql:

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

While this works perfectly for CREATE TABLE, it creates an invalid query for ALTER TABLE: ALTER TABLE postal_address ALTER address_type TYPE VARCHAR(255) CHECK(address_type IN ('private', 'company', 'organisation'));

If at the moment I'd know how to make a valid ALTER TABLE statement using a check constraint I'd gladly provide you with a pull request. But I don't ;(.

If I had to do it manually I would drop the constraint and recreate it but I see that is not an easy thing to to in this context. At the moment the migration works only once and will never work again when a EnumType changes.

Really cool would be real enum support (which is technically possible in Postgres) but neither do I know how to create an enum inside a CREATE TABLE statement. I don't really think that's possible. You'd need CREATE TYPE.

Regards

[fre5h]

@el-seirh Please check this comment https://github.com/fre5h/DoctrineEnumBundle/issues/31#issuecomment-231291275 The key point is bundle cannot create two different SQL statements which are related to one field. Only one definition for field is allowed.

[el-seirh]

That is why only field definition syntax is allowed ALTER TABLE table ALTER field TYPE VARCHAR(255) CHECK(field IN ('value', 'value', 'value'))

That's exactly what I assumed. So ok... only field definition syntax is allowed. But then you still have the problem, that the ALTER TABLE statement is simply invalid. It cannot work. Even if you paste this directly into the database it will not work. Even when the previous check constraint was removed.

What I'm trying to say here is that your mentioned Hook does not work for posgtgres.

So ... we have to agree that it's simply not possible to create a valid field definition statement for postgres once the pseudo-enum has been created?

[liogate]

Hi, this subject is old but I found an alternative to update enum in postgres using migration process.

ALTER TABLE table DROP CONSTRAINT table_column_check;
ALTER TABLE table ADD CONSTRAINT table_column_check CHECK(category IN ('value1','value2'));

Hope this can be helpful.

[flaushi]

so in a migration, one could include

$values = \implode(
            ', ',
            \array_map(
                function ($value) {
                    return "'{$value}'";
                },
                YourEnumType::getValues()
                )
            );
$this->addSql('ALTER TABLE table DROP CONSTRAINT table_column_check');
$this->addSql('ALTER TABLE table ADD CONSTRAINT table_column_check CHECK(category IN ($values))');

Would it be possible to do this automatically when a migration is created (via diff)?