[supbase_flutter 2.3.1] defaultToNull Parameter Not Working as Expected

[MeyerOppelt]

supabase_flutter 2.3.1

Error when bulk inserting with the upsert method, and using the defaultToNull parameter to replace null values with default values.

Code: Some data is redacted for privacy

final listOfChanges = [
{"primary_key": null, "uuid": "e8c192e3-c852-4702-b862-7f1d4a750ef0", "numeric": 50.0, "uuid2": "3c252820-97d4-4a7e-93c5-61547bdd397d"}, 
{"primary_key": null, "uuid": "e3cd1f29-678f-4823-8f71-3a670f89624e", "numeric": 50.0, "uuid2": "cb22b3b7-db5f-4d10-a052-6cc72a38b2db"}
]

await Supabase.instance.client
  .schema("[redacted]")
  .from("[redacted]")
  .upsert(listOfChanges,
    onConflict: "primary_key", defaultToNull: false);

Expected behavior When bulk inserting data with defaultToNull false, Expected behavior is for the client/api to replace null values with default values. The primary_key is defaulted to gen_random_uuid()

Error flutter: null value in column "primary_key" of relation "[redacted]" violates not-null constraint

I am running on MacOs Sonoma 14.1.1

├── supabase_flutter 2.3.1
│   ├── supabase 2.0.6
│   │   ├── functions_client 2.0.0
│   │   ├── gotrue 2.4.1
│   │   ├── postgrest 2.1.0
│   │   ├── realtime_client 2.0.0
│   │   ├── storage_client 2.0.0

Ive also tried running like this, with the primary key as null: (I though maybe the client would act like a sql editor and without specifying the value, would replace it with default)

final listOfChanges = [
{"uuid": "e8c192e3-c852-4702-b862-7f1d4a750ef0", "numeric": 50.0, "uuid2": "3c252820-97d4-4a7e-93c5-61547bdd397d"}, 
{"uuid": "e3cd1f29-678f-4823-8f71-3a670f89624e", "numeric": 50.0, "uuid2": "cb22b3b7-db5f-4d10-a052-6cc72a38b2db"}
]

await Supabase.instance.client
  .schema("[redacted]")
  .from("[redacted]")
  .upsert(listOfChanges,
    onConflict: "primary_key", defaultToNull: false);

Error The error produced from this code is: flutter: UPDATE requires a WHERE clause

[dshukertjr]

We could probably provide a better guide/ docs around how to use onConflict, but if you are specifying the column in onConflict, you need to pass a value for the column you specified.

There is a fine difference between the primary key being set to null and the primary keys not being specified.

In your first case, the primary key is set to null, which is invalid, as primary keys always have to have values.

In your second example, you are not passing the primary key, but are setting onConflict on primary key.

In order to do what you want to do, just use upsert() like this with the default options (without explicitly specifying any options), or add defaultToNull if you want your nullable columns to have non-null values, note that non-nullable columns will get their default values, so you won't have to worry about for example your primary key being null.

final listOfChanges = [
{"uuid": "e8c192e3-c852-4702-b862-7f1d4a750ef0", "numeric": 50.0, "uuid2": "3c252820-97d4-4a7e-93c5-61547bdd397d"}, 
{"uuid": "e3cd1f29-678f-4823-8f71-3a670f89624e", "numeric": 50.0, "uuid2": "cb22b3b7-db5f-4d10-a052-6cc72a38b2db"}
]

await Supabase.instance.client
  .schema("[redacted]")
  .from("[redacted]")
  .upsert(listOfChanges);

[MeyerOppelt]

Thanks @dshukertjr for the reply :)

Unfortunately I get the same UPDATE requires a WHERE clause when using the advice you gave me. I tried some other advice from the JS client, which also yeilded the same results. https://github.com/orgs/supabase/discussions/6804

You said that having an onConflict parameter meant that you had to specify a value for it, although in the JS client it seems to be linked to a DO NOTHING. And looking in the code for the dart client, it seems to just change some search parameters/headers.

If you can explain the behavior of the parameter, I would gladly write some documentation for it.

---

Halfway through writing my comment I solved the issue. Thank you for your help, as I was going to check the query being run, I noticed that a trigger and a function with a malformed UPDATE clause, was causing the error messages.

Have a good one -Meyer