Question 89221 in PostgreSQL Question posted in
The official documentation can be found here.

Migrating string column into array column django postgresql

In my models I have old field tag and created new one tags.

class YOUR_MODEL():
    tag = models.CharField(max_length=64, null=True, default=None, db_index=True, choices=[(tag, tag) for tag in TAGS])
    tags = ArrayField(models.CharField(max_length=64, choices=[(tag, tag) for tag in TAGS]), db_index=True, null=True)

Column tag have only strings, for example ‘APPLE’ or ‘PEAR’.
I want that column tags to be an array of one string after migrations.

Also:

TAGS = field_types.FORM_FIELD_TAGS
FORM_FIELD_TAGS = [
    FORM_FIELD_APPLE_TAG,
    FORM_FIELD_PEAR_TAG,
]
FORM_FIELD_APPLE_TAG = 'APPLE'
FORM_FIELD_PEAR_TAG = 'PEAR'

In my migrations after creating new field and before deleting old one I want to transfer data from column tag into tags. I tried using raw sql: cursor.execute("UPDATE form_builder_formfield SET tags = ARRAY[tag] WHERE tag IS NOT NULL") but this is not working.

I recived errors:
django.db.utils.DataError: malformed array literal: "["APPLE"]"
"[" must introduce explicitly-specified array dimensions.

I also tried using django queryset but I always end with this errors above. Any ideas how to do it?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Okay. So I found a solution. Code was all right. The problem was elsewhere. I had a trigger on a database that was changing '{' curly braces into '[' square braces. So even if I explicitly typed '{' into code it anyway changed it to '[', so postgres couldn't create an array.

    Thanks for trying to help.


  2. 0

    You can add to your migration a RunPython operation. Make a migration, that has 2 operation: adds a new field tags and removes old tag. Between them add a operation bellow:

    migrations.RunPython(migrate_tag_data, reverse_code=reverse_tags),

    You can make migrate_tag_data however you like, but I suggest code bellow. Just put it at start of migration file.

    def migrate_tag_data(apps, schema_editor):
    YOUR_MODEL = apps.get_model("YOUR_APP", "YOUR_MODEL")
    for instance in YOUR_MODEL.objects.all():
        instance.tags = [instance.tag]
        instance.save()


def reverse_tags(apps, schema_editor):
    YOUR_MODEL = apps.get_model("YOUR_APP", "YOUR_MODEL")
    for instance in YOUR_MODEL.objects.all():
        instance.tag = next(instance.tags, None)
        instance.save()

    Then migrate and you’re good!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search