Migrate integer field to string type

Ian W. Scott

unread,

Oct 24, 2016, 3:32:58 PM10/24/16

to web2py-users

I'm using Postgresql and I need to migrate the data type of a field from "integer" to "string". Will it work to just change the field type in the model table definition? What will happen to the existing "integer" data? Any problems foreseen?

Thanks,

Ian

Richard Vézina

unread,

Oct 25, 2016, 10:06:19 AM10/25/16

to web2py-users

You can't just change the field type, you have to create another column of string type then insert your data from a select of the other column into it then delete the old useless column... You need to transform the integer into text at the insert step...

Richard

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Ian W. Scott

unread,

Oct 25, 2016, 11:08:28 AM10/25/16

to web2py-users

Okay, that's what I was afraid of. Thanks.

Ian

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

Dave S

unread,

Oct 25, 2016, 2:20:23 PM10/25/16

to web2py-users

On Tuesday, October 25, 2016 at 8:08:28 AM UTC-7, Ian W. Scott wrote:

Okay, that's what I was afraid of. Thanks.

Ian

On Tuesday, October 25, 2016 at 10:06:19 AM UTC-4, Richard wrote:
You can't just change the field type, you have to create another column of string type then insert your data from a select of the other column into it then delete the old useless column... You need to transform the integer into text at the insert step...

Richard

I would try using a csv export/import. If it's a large table, I can see some pain, but it should be simple in python, awk, or even sed to toss in quotes in the right column.

/dps

Richard Vézina

unread,

Oct 26, 2016, 9:59:16 AM10/26/16

to web2py-users

Yes is you do it within the shell you better commit every 100 records depending of the size of the record, but since it just a single column and integer you can commit even every 500-1000 records I guess without issue...

To do so you can use this snippet :

for i, r in enumerate(db.select()):

db.table.insert(something)

if i % 100 == 0:

db.commit()

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

Reply all

Reply to author

Forward