Django: Using an F expression for a text field in an update call

Question

In a django view, I need to append string data to the end of an existing text column in my database. So, for example, say I have a table named "ATable", and it has a field named "aField". I'd like to be able to append a string to the end of "aField" in a race-condition-free way. Initially, I had this:

tableEntry = ATable.objects.get(id=100) tableEntry.aField += aStringVar tableEntry.save() 

The problem is that if this is being executed concurrently, both can get the same "tableEntry", then they each independently update, and the last one to "save" wins, losing the data appended by the other.

I looked into this a bit and found this, which I hoped would work, using an F expression:

ATable.objects.filter(id=100).update(aField=F('aField') + aStringVar) 

The problem here, is I get an SQL error, saying:

operator does not exist: text + unknown HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. 

Tried changing to "str(aStringVar)" even though its already a string - no luck.. I found a couple django bug reports complaining about similar issues, but I didn't see a fix or a workaround. Is there some way I can cast aStringVar such that it can be appended to the text of the F expression? BTW - also tried "str(F('aField')) + aStringVar" but that converted the result of the F expression to the string "(DEFAULT: )".

Answer 1

You can use the Concat db function.

from django.db.models import Value from django.db.models.functions import Concat  ATable.objects.filter(id=100).update(some_field=Concat('some_field', Value('more string'))) 

In my case, I am adding a suffix for facebook avatars URIs like this:

FACEBOOK_URI = 'graph.facebook.com' FACEBOOK_LARGE = '?type=large' # ... users = User.objects.filter(Q(avatar_uri__icontains=FACEBOOK_URI) & ~Q(avatar_uri__icontains=FACEBOOK_LARGE)) users.update(avatar_uri=Concat('avatar_uri', Value(FACEBOOK_LARGE))) 

and I get SQL like this (Django 1.9):

UPDATE `user_user` SET `avatar_uri` = CONCAT(COALESCE(`user_user`.`avatar_uri`, ''), COALESCE('?type=large', '')) WHERE (`user_user`.`avatar_uri` LIKE '%graph.facebook.com%' AND NOT (`user_user`.`avatar_uri` LIKE '%?type=large%' AND `user_user`.`avatar_uri` IS NOT NULL)) 

The result is all image URIs were changed from http://graph.facebook.com/<fb user id>/picture to http://graph.facebook.com/<fb user id>/picture?type=large

Answer 2

You can override F object in Django with one simple change:

class CF(F):     ADD = '||' 

Then just use CF in place of F. It will place "||" instead of "+" when generating SQL. For example, the query:

User.objects.filter(pk=100).update(email=CF('username') + '@gmail.com') 

will generate the SQL:

UPDATE "auth_user" SET "email" = "auth_user"."username" || '@gmail.com' WHERE "auth_user"."id" = 100