Let's flatten this down a bit. And also make sure we're using the correct database and connection...

It's probably worth making sure that params is a tuple here as per other methods:

the returned parameters are not tuples but arrays. they will be used here: param_rows += [params_on_conflict] in the compiler.py file.

I'm thinking that this name feels a little bit generic...

made a proposal for a name

You should use versionchanged, not versionadded. Also version* annotations are for small notes, not for the entire documentation of new features. You should change a signature in docs and document the new argument directly in the bulk_create() docs.

.. versionchanged:: 5.1

    The `condition` parameter was added.

Moreover docs should be wrapped at 79 chars.

@felixxm is that all I have to sum up in 79 characters?

.. versionadded:: 5.1

   Added the ``condition`` parameter and the ``Excluded`` class.

   The ``condition`` parameter allows specifying a conditional SQL WHERE clause 
   for bulk insert operations involving conflict resolution. This feature is 
   currently supported only by PostgreSQL and SQLite backends (starting from 
   SQLite version 3.42.0) and enables conditional updates based on specified 
   criteria during these operations.

   ``condition`` accepts Django's ``F``, ``Q``, and now ``Excluded`` expressions, 
   providing a secure and consistent way to define conditional logic while ensuring 
   protection against SQL injection and simplifying the construction of complex conditional 
   logic.

   The ``Excluded`` class is a special extension of Django's ``F`` expressions designed for handling
   conflict resolution in bulk insert operations. While F expressions reference fields in the existing
   database, ``Excluded`` refers to values in the proposed data for insertion, particularly in scenarios
   where these values are excluded due to a conflict during an ON CONFLICT clause in SQL.

   Unlike ``F`` expressions, ``Excluded`` cannot be used in general query contexts but is specifically
   tailored for use in ``bulk_create(..., update_conflicts=True)`` scenarios.

   Example using ``F``, ``Q``, and ``Excluded`` expressions:

    .. code-block:: pycon

        >>> from datetime import timedelta
        >>> from django.db.models import F, Q, Excluded
        >>> from django.utils import timezone
        >>> from your_app.models import Item  # Replace 'your_app' with the name of your application
        # Initial insertion with last update date
        >>> items = [
        ...     Item(id=1, name="Item 1", last_updated=timezone.now() - timedelta(days=1)),
        ...     Item(id=2, name="Item 2", last_updated=timezone.now()),
        ... ]
        >>> Item.objects.bulk_create(items)

        # Attempt to update with conditions
        >>> updated_items = [
        ...     Item(id=1, name="New Item 1", last_updated=timezone.now() - timedelta(days=2)),
        ...     Item(id=2, name="New Item 2", last_updated=timezone.now() - timedelta(days=3)),
        ... ]

        >>> Item.objects.bulk_create(
        ...     updated_items,
        ...     update_conflicts=True,
        ...     update_fields=["name", "last_updated"],
        ...     unique_fields=["id"],
        ...     condition=(
        ...         Q(last_updated__gt=F("last_updated") + timedelta(days=1))
        ...         | Q(last_updated__lt=Excluded("last_updated") - timedelta(hours=12))
        ...     ),
        ... )

    In this example, the rows are updated if ``last_updated`` is more recent than the recorded date plus one day,
    or if ``last_updated`` is earlier than the proposed one minus 12 hours ``(Excluded("last_updated") - timedelta(hours=12))``.

how do you explain all that addition in just 79 characters? or do you mean 79 characters per line?

"wrapped at 79 chars" not "limited to the 79 chars", so 79 chars per line.

We cannot hard-code vendor names in expressions. Please add a new feature flag.

I @felixxm understand your concern about hard-coding vendor names. My primary goal is not to check if a backend supports Excluded, but to generate the correct SQL code depending on the backend for use cases like condition=Q(rank__gt=Excluded("number") + 1) & ~Q(name__startswith="A") | Q(number__gt=3).

I am very open to suggestions for improving this implementation. Perhaps we could consider a more abstract method in BaseDatabaseOperations, which can be adapted by each backend to handle Excluded appropriately, while maintaining a uniform and 'Django-esque' interpretation of Excluded across different backends. Your input on the best way forward would be highly valuable

Please add a new feature flag.

Have you seen my comment? ☝️

Please add a new feature flag.

Have you seen my comment? ☝️

I really don't know if we understand each other. I'll suggest something in the next revision and you'll let me know.

Excluded makes sense only for backend that support supports_update_conflicts_with_condition, so there is not need to check vendor names you should check supports_update_conflicts_with_condition flag in the Excluded.as_sql(), e.g.

def as_sql(self, compiler, connection):
    if not connection.features.supports_update_conflicts_with_condition:
        raise NotSupportedError(...)

Excluded makes sense only for backend that support supports_update_conflicts_with_condition, so there is not need to check vendor names you should check supports_update_conflicts_with_condition flag in the Excluded.as_sql(), e.g.

def as_sql(self, compiler, connection):
    if not connection.features.supports_update_conflicts_with_condition:
        raise NotSupportedError(...)

yes indeed. you're right. i hadn't thought of that.
However, our discussions have made me rethink my approach to the question. But I'll make a note of it in case I need to keep that approach.

Compilation should be handled in the SQLInsertCompiler.as_sql(). There is no need to add a backend-specific hook for this, there is nothing backend-specific in the compilation, and there is no need to pass expression/query/sql/params back and forth. Moreover, moving it back to the SQLInsertCompiler.as_sql() will allow avoiding all unnecessary changes in DatabaseOperations.

Compilation should be handled in the SQLInsertCompiler.as_sql(). There is no need to add a backend-specific hook for this, there is nothing backend-specific in the compilation, and there is no need to pass expression/query/sql/params back and forth. Moreover, moving it back to the SQLInsertCompiler.as_sql() will allow avoiding all unnecessary changes in DatabaseOperations.

@ngnpope @felixxm I must have misinterpreted it then:
#17515 (comment)

Why it's not a subclass of F?

Hi @felixxm, initially, Excluded was indeed a subclass of F. However, we encountered some limitations, particularly with the SQLite backend. When using operations like Excluded('rank') + 1, we found errors, for example, the absence of the _output_field_or_none attribute in Excluded objects. To solve these problems, Excluded was redesigned to inherit directly from Expression. This approach enabled us to handle the specifics of excluded values in conflicting update operations, while avoiding F inheritance problems."

And why you need output_field? Excluded doesn't depend on the output field or uses it.

You also documented Excluded as an extension of F which is currently not True.

And why you need output_field? Excluded doesn't depend on the output field or uses it.

here's my class as I originally defined it

class Excluded(F):
    def __init__(self, field_name):
        super().__init__(field_name)
        if isinstance(field_name, str):
            self.lhs = F(field_name)
        else:
            self.lhs = field_name

    def resolve_expression(
        self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
    ):
        c = self.copy()
        c.lhs = self.lhs.resolve_expression(
            query, allow_joins, reuse, summarize, for_save
        )
        return c

    def as_sql(self, compiler, connection):
        lhs_sql, lhs_params = compiler.compile(self.lhs)
        db_vendor = connection.vendor

        if db_vendor in ["postgresql", "sqlite"]:
            sql = f"EXCLUDED.{lhs_sql.split('.')[-1]}"
        elif db_vendor == "mysql":
            sql = f"VALUES({lhs_sql.split('.')[-1]})"
        else:
            raise NotImplementedError(
                f"Backend '{db_vendor}' is not supported for the Excluded operation."
            )

        return sql, lhs_params

    def copy(self):
        copy = super().copy()
        copy.lhs = self.lhs
        return copy

it works for this kind of operation

UpsertConflict.objects.bulk_create(
        updated_items,
        update_conflicts=True,
        update_fields=["name", "rank"],
        unique_fields=["number"],
        condition=Q(rank__gt=Excluded("number") ) & ~Q(name__startswith="A") | Q(number__gt=3)
    )

but not for

UpsertConflict.objects.bulk_create(
        updated_items,
        update_conflicts=True,
        update_fields=["name", "rank"],
        unique_fields=["number"],
        condition=Q(rank__gt=Excluded("number") +1) & ~Q(name__startswith="A") | Q(number__gt=3)
    )

I remind you that it's on the sqlite backend that the problem lies.

Why Excluded is not required on PostgreSQL?

Why do we need Excluded to be a subclass of F?

Because it's natural, the only difference between F and Excluded is that you want to use EXCLUDED instead of a table name.

I remind you that it's on the sqlite backend that the problem lies.

Why Excluded is not required on PostgreSQL?

Why do we need Excluded to be a subclass of F?

Because it's natural, the only difference between F and Excluded is that you want to use EXCLUDED instead of a table name.

allow me to submit my current work for further review and discussion. i feel there is a lot to be said for the keyword "Excluded".
Besides, I think there must be a serious problem under the hood of F. We'll talk about it later.

As a suggestion, you might want to reduce the scope of this PR to getting condition into bulk_create which supports F and Q expressions. Then once that's landed, you can create a new PR with adding Excluded support. - was not a good suggestion sorry

@HamaBarhamou I would advise to pursue Mariusz suggestions and subclass F. I hear you're saying there are some test failures but we can update the solution as we go and figure those out.
Mariusz has years and years of Django ORM experience. I've always ended with something that has a much cleaner design after applying his feedback. We're aiming for something intuitive and maintainable as well as well functioning. So although you might feel like this is a backwards step, it might be one step back to go two steps forward.

Trying something else doesn't mean this doesn't work - we can always come back to this. ❤️

As a suggestion, you might want to reduce the scope of this PR to getting condition into bulk_create which supports F and Q expressions. Then once that's landed, you can create a new PR with adding Excluded support. - was not a good suggestion sorry

@HamaBarhamou I would advise to pursue Mariusz suggestions and subclass F. I hear you're saying there are some test failures but we can update the solution as we go and figure those out. Mariusz has years and years of Django ORM experience. I've always ended with something that has a much cleaner design after applying his feedback. We're aiming for something intuitive and maintainable as well as well functioning. So although you might feel like this is a backwards step, it might be one step back to go two steps forward.

Trying something else doesn't mean this doesn't work - we can always come back to this. ❤️

I agree with you @sarahboyce . But my problem is that I need a solution that works, which is not the case with @felixxm's proposal, and I had already implemented this approach without success. Normally the simple fact of inheriting from F should allow me to do operations like Ecluded('rank')+1. Perhaps this is a deeper problem that requires a deeper understanding of F. I'll try again to solve the problem with Ecluded as a subclass of F.
But if it doesn't work, I can't see the point of leaving a solution that does work to take a solution that has proven limits.

@felixxm , @sarahboyce this approach seems to work.

class Excluded(F):
    def resolve_expression(self, *args, **kwargs):
        super().resolve_expression(*args, **kwargs)
        self.output_field = None
        return self

    def as_sql(self, compiler, connection):
        if not connection.features.supports_excluded_expression:
            raise NotSupportedError(
                f"{connection.vendor} doesn't support partial updating rows on "
                "conflicts during INSERT"
            )
        quoted_field_name = compiler.quote_name_unless_alias(self.name)
        sql = f"EXCLUDED.{quoted_field_name}"
        return sql, []

    def __repr__(self):
        return f"{self.__class__.__name__}({self.name})"

    @cached_property
    def output_field(self):
        """Return the output type of this expressions."""
        output_field = self._resolve_output_field()
        if output_field is None:
            self._output_field_resolved_to_none = True
            raise FieldError("Cannot resolve expression type, unknown output_field")
        return output_field

    @cached_property
    def _output_field_or_none(self):
        """
        Return the output field of this expression, or None if
        _resolve_output_field() didn't return an output type.
        """
        try:
            return self.output_field
        except FieldError:
            if not self._output_field_resolved_to_none:
                raise

I only copied the functionsdef output_field(self)and def _output_field_or_none(self) from the BaseExpression class into Excluded.
knowing that Excluded inherits from F and F from Combinable and Combinable from nothing, and that BaseExpression doesn't intervene in the inheritance sequence, I wonder why Excluded needs output_field to work when it shouldn't.
But hey, it works, so I'm fine with it

Also, this is untested.

Also, this is untested.

I don't understand. What hasn't been tested?

I'm not sure how to say it clearer 🤔 This line is untested. Raised error is untested.

i've rethought this approach. i'll update within 24 hours. hope you enjoy.

There is nothing to reconsider. Tests are required for new lines.

There is nothing to reconsider. Tests are required for new lines.

I'll make a note.

hi @felixxm i'm looking for a way to reproduce exactly the production environment on my machine for test execution. would you have a suggestion.

Please move it directly to the Excluded.as_sql() as I requested:

    def as_sql(self, compiler, connection):
        if not connection.features.supports_update_conflicts_with_condition:
            raise NotSupportedError(
                f"{connection.vendor} doesn't support partial updating rows on "
                "conflicts during INSERT"
            ) 
        ...

This is untested

This is still required from this comment.

I think we should probably document Excluded separately in docs/ref/models/expressions.txt
Then we can have people link to the Excluded docs specifically
This would have a versionadded note