Popolate existing DB entries on Migration with values when adding new columns

[Daniela Festi]

Hi,

I need to add a new column with default values to an existing table.

In the model file, I add

SQLField('newcolumn', type='text', default= 'Default text' )

to the db.define_table('mytest', ...) command.

After migration, all newly inserted tuples get 'Default text' in the column 'newcolumn' and the SQLForms are pre-populated with that value, which is the desired behavior.

However, all existing tuples in the table have a NULL value in that column, which is not desired.

The sql.log shows that the migration command is:

ALTER TABLE "mytest" ADD "newcolumn" CHAR(512);

What I would need instead is:

ALTER TABLE "mytest" ADD "newcolumn" CHAR(512) DEFAULT "Default text";

So my question is: How do I populate existing tuples with default values when migrating?

Is there an elegant, web2py supported way or do I have to do it manually?

And if I have to do it manually: Is there a good recipe for it?

My current idea would be to turn migration off on the productive system, perform migration on the testing system, copy and edit the commands from the sql.log on the testing system and run them on the DB system of the productive system (which is Postgres).

However, there might be better ways to do that ...

I already searched the web2py book and this forum for that issue, but I only found a recipe on how to Populate on Deployment (http://web2py.com/books/default/chapter/29/13/deployment-recipes#Populating-a-Database-on-Deployment), not on Migration.

Any hints would be highly welcome!

Daniela

[valq...@gmail.com]

Field(...,  notnull = True) does the trick

суббота, 16 января 2021 г. в 21:39:00 UTC+3, ferm...@googlemail.com:

[Daniela Festi]

Since I want to allow users to empty the field later, the workflow would be:

- add "notnull=True" to the definition

- update code, perform the migration

- remove "notnull=True" from the defintion

- update code again

Still not very elegant, but maybe easier than my previous recipe. Thanks for that idea!