Using Django ORM to Mimic PostgreSQL's Conflict Handling Behavior

Based on the Django 5.1 documentation, the bulk_create() method with ignore_conflicts=True will indeed return all objects that were passed to it, regardless of whether they were actually inserted or not. This differs from PostgreSQL's native behavior where only successfully inserted records are returned.

Unfortunately, Django's ORM doesn't provide a direct way to mimic PostgreSQL's behavior of returning only the truly inserted records when using bulk_create() with ignore_conflicts=True. The method is designed to be database-agnostic, which means it doesn't expose the specific PostgreSQL behavior you're looking for.

Possible Workarounds

1. Use a Raw SQL Query

If you need the exact PostgreSQL behavior, you could use a raw SQL query:

PYTHONCopied
from django.db import connection

def bulk_create_with_returning(model_class, objects, unique_fields):
    # Prepare data for insertion
    field_names = [f.name for f in model_class._meta.fields if f.name != 'id']
    values = []
    for obj in objects:
        row = [getattr(obj, field) for field in field_names]
        values.append(row)
    
    # Construct SQL query with ON CONFLICT DO NOTHING and RETURNING
    placeholders = ', '.join(['%s'] * len(field_names))
    fields_str = ', '.join([f'"{f}"' for f in field_names])
    conflict_fields = ', '.join([f'"{f}"' for f in unique_fields])
    
    sql = f"""
    INSERT INTO {model_class._meta.db_table} ({fields_str})
    VALUES ({placeholders})
    ON CONFLICT ({conflict_fields}) DO NOTHING
    RETURNING id
    """
    
    # Execute query and get inserted IDs
    with connection.cursor() as cursor:
        inserted_ids = []
        for row in values:
            cursor.execute(sql, row)
            result = cursor.fetchone()
            if result:
                inserted_ids.append(result[0])
    
    # Return only the objects that were inserted
    return model_class.objects.filter(id__in=inserted_ids)

2. Check Existence Before Insertion

Another approach is to check which objects already exist before attempting to insert them:

PYTHONCopied
def bulk_create_only_new(model_class, objects, unique_fields):
    # Identify which objects already exist
    existing = set()
    for field in unique_fields:
        values = [getattr(obj, field) for obj in objects if hasattr(obj, field)]
        query = {f"{field}__in": values}
        existing_objs = model_class.objects.filter(**query).values_list(*unique_fields, flat=True)
        existing.update(existing_objs)
    
    # Filter out objects that already exist
    new_objects = [obj for obj in objects if not any(
        getattr(obj, field) in existing for field in unique_fields
    )]
    
    # Bulk create only new objects
    return model_class.objects.bulk_create(new_objects)

Limitations

Both workarounds have limitations:

1. The raw SQL approach is PostgreSQL-specific and less portable
2. The existence check approach requires additional queries and may not be as efficient for large datasets
3. Neither solution perfectly replicates PostgreSQL's atomic behavior

If you need the exact PostgreSQL behavior and performance characteristics, you might need to use raw SQL or consider if your application architecture could be adjusted to work with Django's behavior.

Filtering by Computed Properties in Django

You can't directly filter on a property like extra using filter() because properties are computed in Python after the database query has been executed. However, there are several approaches to achieve what you want:

1. Using extra() method (not recommended)

You could use the extra() method to add a calculated field to your query:

PYTHONCopied
Imagine.objects.extra(
    select={'calculated_extra': '100 * priority'}
).filter(calculated_extra=200)

However, this approach is not recommended as extra() is an old API that Django aims to deprecate in the future. It should only be used as a last resort.

2. Using annotate() with expressions (recommended)

A better approach is to use annotate() with Django's F expressions:

PYTHONCopied
from django.db.models import F, ExpressionWrapper, IntegerField

Imagine.objects.annotate(
    calculated_extra=ExpressionWrapper(F('priority') * 100, output_field=IntegerField())
).filter(calculated_extra=200)

This approach is database-efficient as the calculation happens at the database level, not in Python.

3. Using RawSQL (alternative to extra)

If you need more complex SQL, you can use RawSQL with annotate():

PYTHONCopied
from django.db.models.expressions import RawSQL

Imagine.objects.annotate(
    calculated_extra=RawSQL("100 * priority", params=(), output_field=IntegerField())
).filter(calculated_extra=200)

This is equivalent to the extra() example but uses the more modern API.

4. Filter in Python (inefficient for large datasets)

For small datasets, you could retrieve all objects and filter in Python:

PYTHONCopied
# Not recommended for large datasets
filtered_objects = [obj for obj in Imagine.objects.all() if obj.extra == 200]

This is inefficient for large datasets as it retrieves all records from the database.

The recommended approach is using annotate() with expressions as it's the most maintainable and follows Django's best practices.