Unique constraint not found in bulk_create's unique fields ?

Django Internals ORM
1

Hello

Using Django 5.2 and PSQL 16.4

I have a model defined like so:

class Foo(models.Model):    
    field_A = models.Charfield(blank=True, default=””)
    field_B = models.ForeignKey(Hello, null=True, blank=True)
    field_C = models.ForeignKey(World, null=True, blank=True)
    field_D = models.Charfield(blank=True, default=””)
    value_in_need_to_update = models.FloatField()

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=[
                    "field_A",
                    "field_C",
                ],
                name="unique_AC",
            ),
            models.UniqueConstraint(
                fields=[
                    "field_A",
                    "field_B",
                    "field_D",
                ],
                condition=~models.Q(   # Here the condition is important
                    field_D="",
                ),
                name="unique_ABD",
            ),
        ]

My 2nd unique constraint is conditional: this tuple (A, B, D) must be unique only if D isn’t ””

In my data pipeline I do this:

Foo.objects.bulk_create(
    objs,
    update_conflicts=True,
    update_fields=["value_in_need_to_update"],
    unique_fields=[  # The "unique_ABD constraint"
        "field_A",
        "field_B",
        "field_D",
    ]
)

However Django throws me

...
psycopg.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification
...
django.db.utils.ProgrammingError: there is no unique or exclusion constraint matching the ON CONFLICT specification

It seems that the conditional UNIQUE constraint isn’t found ? Am I missing something ?

Thank you very much

2

This one’s a bit of a tricky one and can confuse folks.

iirc – Postgres partial unique indexes aren’t technically constraints – and cannot be used as such – as opposed to non-partial unique index which are.

Edit: I’m just reviewing the Postgres documentation for possible alternatives because Django doesn’t fully pass through all the upsert options.

3

Ah so I’m sort of 1/2 right :sweat_smile:

The part I was talking about wrt to constraints is when you want to specify your conflict target as the constraint name.

With index inference, the upsert using a partial index must supply the predicate. Details on the PG docs: PostgreSQL: Documentation: 17: INSERT

Django doesn’t have a param for the predicate :frowning:

(So it’s good old raw sql for you :sweat_smile: )

Maybe we should submit a “new-features” issue to ask for one?

4

Results if you’re interested:

sample=# \d upsert_partial_foo
                                   Table "public.upsert_partial_foo"
         Column          |       Type        | Collation | Nullable |             Default
-------------------------+-------------------+-----------+----------+----------------------------------
 id                      | bigint            |           | not null | generated by default as identity
 field_A                 | character varying |           | not null |
 field_B                 | integer           |           |          |
 field_C                 | integer           |           |          |
 field_D                 | character varying |           | not null |
 value_in_need_to_update | double precision  |           | not null |
Indexes:
    "upsert_partial_foo_pkey" PRIMARY KEY, btree (id)
    "unique_ABD" UNIQUE, btree ("field_A", "field_B", "field_D") WHERE NOT "field_D"::text = ''::text
    "unique_AC" UNIQUE CONSTRAINT, btree ("field_A", "field_C")

sample=# insert into upsert_partial_foo ("field_A", "field_B", "field_C", "field_D", "value_in_need_to_update") values ('A', 1, 2, 'D', 1.0);
INSERT 0 1
sample=# INSERT INTO "upsert_partial_foo" ("field_A",
                                  "field_B",
                                  "field_C",
                                  "field_D",
                                  "value_in_need_to_update")
VALUES ('A', 1, 3, 'D', 2.0)
ON conflict ("field_A", "field_B", "field_D") where NOT "field_D"::text = ''::text
DO UPDATE
SET "value_in_need_to_update" = excluded."value_in_need_to_update" RETURNING "upsert_partial_foo"."id";
 id
----
  8
(1 row)

INSERT 0 1
sample=# table upsert_partial_foo;
 id | field_A | field_B | field_C | field_D | value_in_need_to_update
----+---------+---------+---------+---------+-------------------------
  8 | A       |       1 |       2 | D       |                       2
(1 row)
5

Thank you so much for investigating my issue !

How would that predicate work ? Q objects ? Referencing the constraint name ?

I’m all for a new feature in django, but I’ve troubles digesting it to make a coherent feature request

6

Looks like the only way is raw SQL until Django supports it.

Don’t worry about submitting a feature request, I’ve already done so here :wink: Upsert conflict target index predicate · Issue #79 · django/new-features · GitHub

1 Like
7

I suspect this relates to this issue which is already accepted and suggest deprecating unique_fields: tuple[str] in favor of unique_expressions: tuple[Expression] or even a unique_constraint: str | tuple[str | Expression] kwarg that accepts either a constraint name, or a tuple mixing field and expressions.

With this approach you could simply do

Foo.objects.bulk_create(
    objs,
    update_conflicts=True,
    update_fields=["value_in_need_to_update"],
    unique_constraint="unique_ABD",
)

and Django could automatically add the conditional index predicate.