Upsert returning null value error

[jayehernandez]

Hi! I am trying to upsert in my table which should allow either updating an existing row, or inserting a new row if it doesn't have an ID yet. This is how I'm doing it:

const { error } = await supabase
      .from('my_table')
      .upsert([{ id: 3, name: 'Abc' }, { name: 'Def' }], { onConflict: 'name' });

However, I get this error when I try to combine an update and an insert. It works fine if it's just all updates or if it's just all inserts.

Error: null value in column "id" of relation "my_table" violates not-null constraint

I also have the ID as an identity as shown in this:

Would appreciate your help, thanks!

[steve-chavez]

Hello,

Can you try with defaultToNull: false?

const { error } = await supabase
      .from('my_table')
      .upsert([{ id: 3, name: 'Abc' }, { name: 'Def' }], { onConflict: 'name', defaultToNull: false});

This is a recent feature so you might have to upgrade your instance by pausing/unpausing.

[jayehernandez]

This is exactly what I needed, thank you!

[kno-raziel]

I spent time debugging a similar issue because the documentation is not clear about this option. At least not about what is the default value of the option.

[steve-chavez]

Hm, perhaps we should make defaultToNull: false the default for the next major version.

cc @soedirgo

[soedirgo]

Sounds fine DX-wise, but iirc there's a perf hit when you use it?

[soedirgo]

(Though I'd still push for it regardless)

[Cali93]

Hey, I'm facing the same issue even if I use defaultToNull: false.

Client version: "@supabase/supabase-js": "^2.38.0"

Here's my table definition

CREATE TABLE my_table (
    id UUID UNIQUE PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    description VARCHAR(3000) NOT NULL
);

But after running this in the Supabase editor, when I inspect the definition on the Supabase UI I see that the the table definition has a NOT NULL constraint

id uuid not null default uuid_generate_v4 (),

And here's the response I get if I don't provide the id on my upsert.

{
  "code":"23502", 
  "details":"Failing row contains (null, Not working, Annoying).",
  "hint":null,
  "message":"null value in column \"id\" of relation \"my_table\" violates not-null constraint"
}

[GerritPlehn]

Make sure your Project is running a PostgREST version >= 11.1, that was the issue for me. You can check it on the "Infrastructure" tab in your project settings.
That version introduced Bulk Inserts with default values, see release notes