Upsert failing on non-null column, even though value exists in db

[MaximusMcCann]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I want to bulk update, but must use upsert. I use upsert but receive issues on non-null columns when they are not provided, despite the value existing on the record in the db.

To Reproduce

Example table:

CREATE TABLE test_table (
  id text primary key,
  other_column text null,
  name text not null --<--- The problem
);

INSERT INTO test_table (id, other_column, name)
VALUES ('id1',null,'some_name');

Updating a single row (via upsert): I know the record exists and the name column is populated. However if I try to upsert:

await supabase
.from('test_table')
.upsert([
{ id: 'id1', other_column: 'other_column_text' }
])

I receive

{
  code: '23502',
  details: 'Failing row contains (id1, other_column_text, null).',
  hint: null,
  message: 'null value in column "name" of relation "test_table" violates not-null constraint'
}

Expected behavior

The row show be "merged", thus it should just update the two columns I provided, and not default the name column to null, which in this case is not allowed.

• OS: macOS
• "@supabase/ssr": "^0.0.10",
  "@supabase/supabase-js": "^2.39.2",
• Version of Node.js: v20.10.0

[evelant]

I'm seeing very broken behavior with upsert, possibly related. I have a trigger that checks if the incoming NEW.version < OLD.version to reject updates from clients without the latest data. When I use upsert NEW.version is somehow set to the wrong value even though I've verified that the data I pass to upsert is correct! I pass { ...rest_of_record, version: 4 } to upsert and my trigger fails because NEW.version is 1 not 4.

[oddanderson]

I am seeing several issues with upsert as well. Is there a timeline on fixing this? There is no other good method for performing bulk row updates.

It's concerning that this issue has remained open this long.

[pwc-mw]

Having weird behavior on upsert as well (using python client):

• bulk upsert of more than 10 rows throw error : violates not-null constraint on {column}
• however loop upsert doesn't throw any error.
• the column with constraint not null has a default value, hence if value missing shouldn't throw an error (I mean field not set and no field = None).

[steve-chavez]

@pwc-mw If the column has a default, there's a defaultToNull argument that you can use to solve that case:

await supabase
.from('test_table')
.upsert([
{ data: "here"},
{ data: "here"},
{ data: "here"}
], {
  defaultToNull: false
})

See the options "details" on https://supabase.com/docs/reference/javascript/upsert

defaultToNull is true by default because we needed to maintain backwards compatibility, but there are plans to change it to false for supabase-js v3.