Postgres UniqueConstraint update order

Hey guys, I’ve been trying to use UniqueConstraint on my project and although it works, I ran into an issue that I would like your expertise to figure out.

My project has a rule where each user can only have 1 favorite query, so while scanning the internet I discovered UniqueConstraint since I’m using a Postgres database I could use the condition setting, I also ran into this piece of code:

from django.db.models import BooleanField, ExpressionWrapper, Q

    is_favorite=ExpressionWrapper(Q(pk=my_pk), BooleanField())

Which is supposed to change is_favorite to False from any user and set it to True to the specified one.
My problem is that the above piece of code does not work, it can change the statuses of queries but it returns an error after a query goes from False → True → False → True, so instead, I’m having to run 2 queries, one to set everything to False and another to set it to True.

I had some discussions about this on stackoverflow but we couldn’t figure out why the code wouldn’t work, why we were getting errors. I’ll post some code snippets but I advise anyone interested in this to also read the StackOverflow discussion.


class QueryModel(models.Model):

    id = models.AutoField(_("query id"), primary_key=True)
    user = models.ForeignKey(UserModel, on_delete=models.CASCADE)
    name = models.CharField(_("query name"), max_length=150)
    status = models.CharField(
        _("query status"),
    is_favorite = models.BooleanField(_("favorite"), default=False)
    date_created = models.DateTimeField(_("date created"), auto_now_add=True)

    class Meta(object):
        app_label = "app"
        db_table = "query"
        constraints = [


class QueryView(viewsets.ModelViewSet):
    View to list all users in the system.

    * Requires token authentication.
    * Only admin users are able to access this view.

    authentication_classes = [TokenAuthentication]
    permission_classes = [IsAuthenticated]
    serializer_class = QuerySerializer
    aws_client = AWSClient()

    def get_queryset(self):
        This view should return a list of all the purchases
        for the currently authenticated user.
        self._paginator = None
        return QueryModel.objects.filter(user=self.request.user).order_by("id")

    def create(self, request):
        serializer = QuerySerializer(


        return Response(, status=status.HTTP_201_CREATED)

    def partial_update(self, request, pk=None):
        serializer = QuerySerializer(, partial=True)

        if "is_favorite" in serializer.validated_data.keys():
                is_favorite=ExpressionWrapper(Q(pk=pk), BooleanField())

        return super().partial_update(request, pk)


router = DefaultRouter(trailing_slash=False)
router.register("auth", AuthView, basename="auth")
router.register("query", QueryView, basename="query")

version_info = json.load(open("./version.json"))

def health_check(request):
    """Check if the endpoint is on. Used by container orchestration system."""
    return Response({"status": "pass", **version_info})

urlpatterns = [
    path("", health_check),


Traceback (most recent call last):
   File "/usr/local/lib/python3.11/site-packages/django/db/backends/", line 105, in _execute
     return self.cursor.execute(sql, params)
 psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "unique_favorite_per_user"
 DETAIL:  Key (user_workspace_id)=(2) already exists.
 The above exception was the direct cause of the following exception:
 Traceback (most recent call last):
   File "/usr/local/lib/python3.11/site-packages/django/core/handlers/", line 55, in inner
     response = get_response(request)
   File "/usr/local/lib/python3.11/site-packages/django/core/handlers/", line 197, in _get_response
     response = wrapped_callback(request, *callback_args, **callback_kwargs)
   File "/usr/local/lib/python3.11/site-packages/django/views/decorators/", line 65, in _view_wrapper
     return view_func(request, *args, **kwargs)
   File "/usr/local/lib/python3.11/site-packages/rest_framework/", line 124, in view
     return self.dispatch(request, *args, **kwargs)
   File "/usr/local/lib/python3.11/site-packages/rest_framework/", line 509, in dispatch
     response = self.handle_exception(exc)
   File "/usr/local/lib/python3.11/site-packages/rest_framework/", line 469, in handle_exception
   File "/usr/local/lib/python3.11/site-packages/rest_framework/", line 480, in raise_uncaught_exception
     raise exc
   File "/usr/local/lib/python3.11/site-packages/rest_framework/", line 506, in dispatch
     response = handler(request, *args, **kwargs)
   File "/var/lib/trends_backend/trends/views/", line 61, in partial_update
   File "/usr/local/lib/python3.11/site-packages/django/db/models/", line 1253, in update
     rows = query.get_compiler(self.db).execute_sql(CURSOR)
   File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/", line 1990, in execute_sql
     cursor = super().execute_sql(result_type)
   File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/", line 1562, in execute_sql
     cursor.execute(sql, params)
   File "/usr/local/lib/python3.11/site-packages/django/db/backends/", line 122, in execute
     return super().execute(sql, params)
   File "/usr/local/lib/python3.11/site-packages/django/db/backends/", line 79, in execute
     return self._execute_with_wrappers(
   File "/usr/local/lib/python3.11/site-packages/django/db/backends/", line 92, in _execute_with_wrappers
     return executor(sql, params, many, context)
   File "/usr/local/lib/python3.11/site-packages/django/db/backends/", line 100, in _execute
     with self.db.wrap_database_errors:
   File "/usr/local/lib/python3.11/site-packages/django/db/", line 91, in __exit__
     raise dj_exc_value.with_traceback(traceback) from exc_value
   File "/usr/local/lib/python3.11/site-packages/django/db/backends/", line 105, in _execute
     return self.cursor.execute(sql, params)
 django.db.utils.IntegrityError: duplicate key value violates unique constraint "unique_favorite_per_user"
 DETAIL:  Key (user_id)=(2) already exists.

Indexes created by Django Migrate:

CREATE UNIQUE INDEX unique_favorite_per_user ON trends.query USING btree (user_id) WHERE is_favorite

If you guys need anymore information let me know and I’ll update the post.