Jira issue originally created by user mathias.strasser:
Is that possible to change the request when we would like to change the type of a column in PostgreSQL via Symfony 2 ?
app/console doctrine:schema:update --force
An exception occurred while executing 'ALTER TABLE foo ALTER valid TYPE BOOLEAN':
SQLSTATE: Datatype mismatch: 7 ERROR: column "valid" cannot be cast to type boolean
For resolve this problem I must execute manually this command :
ALTER TABLE foo ALTER valid TYPE BOOLEAN USING valid::BOOLEAN;
I've asked this question to DoctrineBundle, but they redirected to you (doctrine/DoctrineBundle#292)
Comment created by @ocramius:
[~mathias.strasser] does this happen also when data is not in the table?
To be honest, this exception seems valid to me, as it actually prevents you from applying destructive DDL on your schema.
Comment created by @deeky666:
I tend to agree with [~ocramius]. I know of similar issues with other vendors, too and IMO this exception is perfectly valid as you would otherwise risk to loose data integrity. How would you expect a database to do data conversion between non-compatible types? Maybe you can force type conversion for some types but I don't think it is the task of DBAL to handle this. You should handle this manually IMO.
Comment created by mathias.strasser:
I understand this prevent but I think if a user asked to apply a schema update, it should be applied or he should get prompted for a confirmation.
It think it's a pity we are forced to manually perform this query if we are sure.
What do you think ?
[~mathias.strasser] I understand your concern. But this is an exception coming from the database server. How would you expect Doctrine to do the data conversion? Maybe in your case when converting to a BOOLEAN column, this could somehow work (even at database level). But how would you for example expect Doctrine to convert a STRING column to INTEGER for example. You just can't do that. Also you have to keep in mind that DBAL is an abstraction layer and thus requires a cross-vendor behaviour that is the same for all. It might be that PostgreSQL can force the conversion of any column type to BOOLEAN but other vendors can't do this natively. You could adopt the scenario to PHP and type casting. It's a similar scenario.
Won't be fixed.
As I've previously noted, this is an engine error that simply avoids a lossy conversion.
The conversion should be forced manually by the person running the schema tool (by manually running the dump command and applying the DDL statements)
Issue was closed with resolution "Won't Fix"
I quite understand.
Thanks for your replies.