Migration script 11.0.0-12.0.0.sql fails when upgrading

[GMellar]

I'm using postgresql 9.6.17. The following error came up:

DB_ERROR_SYNTAX: DROP INDEX ix_fk_product_stockALTER TABLE llx_actioncomm DROP COLUMN punctual;
ERROR: 42601: syntax error at or near "TABLE" LINE 1: DROP INDEX ix_fk_product_stockALTER TABLE llx_actioncomm DRO... ^ LOCATION: scanner_yyerror, scan.l:1127

DB_ERROR_42804: ALTER TABLE llx_extrafields MODIFY COLUMN printable integer DEFAULT 0;
ERROR: 42804: column "printable" cannot be cast automatically to type integer HINT: You might need to specify "USING printable::integer". LOCATION: ATPrepAlterColumnType, tablecmds.c:8056

[GMellar]

Thanks for the quick fix. I tried it but there seems to be one error left:

DB_ERROR_42804: ALTER TABLE llx_extrafields MODIFY COLUMN printable integer DEFAULT 0;
ERROR: 42804: column "printable" cannot be cast automatically to type integer HINT: You might need to specify "USING printable::integer". LOCATION: ATPrepAlterColumnType, tablecmds.c:8056

Somehow postgresql does not like that when modifying a column.

[smonff]

I also had problems during the migration with PostgreSQL for casting from BOOLEAN to INTEGER. I updated the script with:

ALTER TABLE llx_extrafields ADD COLUMN printable_int integer DEFAULT 0;
ALTER TABLE llx_extrafields DROP COLUMN printable;
ALTER TABLE llx_extrafields RENAME COLUMN printable_int TO printable;

It should work if you have no data in this table. Otherwise something like this should be considered.

[GMellar]

In my case there are data inside this table. Is it possible to use database agnostic migration files? Up until now I never had any problems with upgrading a major version of dolibarr. I will have a look into the provided link. Thanks for the help so far.

…

On 03.07.20 14:13, Sébastien Feugère wrote: I also had problems during the migration with PostgreSQL for cqasting to |BOOLEAN| to |INTEGER|. I updated the script with: |ALTER TABLE llx_extrafields ADD COLUMN printable_int integer DEFAULT 0; ALTER TABLE llx_extrafields DROP COLUMN printable; ALTER TABLE llx_extrafields RENAME COLUMN printable_int TO printable; | It should work if you have no data in this table. Otherwise something like this <https://www.ezvan.fr/psql-conversion.html> should be considered. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#14142 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACPT767P6DVWWTSU3GI46KTRZXDQNANCNFSM4OKSN3FA>.

[dpriskorn]

This was fixed in 48adeb9. Please close