Upsert does not work with not null with default

[RentfireFounder]

Bug report

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

Describe the bug

To Reproduce

I am upserting the data like this

export const updateChecklistData = async (values: BlueprintChecklistInsert[]) => {
    const { error } = await supabase.getClient().from('blueprint_checklist').upsert(values, { onConflict: 'id' });
    if (error) {
        throw error;
    }
};

here if I am doing just insert with data, something like this

   {
        "title": "Keyboard",
        "blueprint_id": "f373e251-da55-4d3d-b4f4-69b4c7b26c7a",
        "id": "030d70a9-e681-476d-a9c4-c3a9ef1399ec",
        "order": 1,
        "parent_id": "9a09f8c7-b255-4725-a228-e5b8f79404c1",
        "event_type": "creation",
        "workspace_id": "7db6e71c-a24c-4710-b63a-728c9413a0cf"
    }

this will succeed but if I update/as well as insert, it will throw an error saying

null value in column "created_at" of relation "blueprint_checklist" violates not-null constraint

[
    {
        "id": "b8b1f216-13c9-446c-b2d5-84881ff5cac4",
        "created_at": "2024-07-27T19:10:04.526662+00:00",
        "title": "Laptop",
        "blueprint_id": "f373e251-da55-4d3d-b4f4-69b4c7b26c7a",
        "workspace_id": "7db6e71c-a24c-4710-b63a-728c9413a0cf",
        "event_type": "creation",
        "description": null,
        "order": 0,
        "parent_id": "9a09f8c7-b255-4725-a228-e5b8f79404c1",
        "remind": null,
        "archived": false
    },
    {
        "title": "Keyboard",
        "blueprint_id": "f373e251-da55-4d3d-b4f4-69b4c7b26c7a",
        "id": "030d70a9-e681-476d-a9c4-c3a9ef1399ec",
        "order": 1,
        "parent_id": "9a09f8c7-b255-4725-a228-e5b8f79404c1",
        "event_type": "creation",
        "workspace_id": "7db6e71c-a24c-4710-b63a-728c9413a0cf"
    },

My table something like this

create table "public"."blueprint_checklist" (
    "id" uuid not null default gen_random_uuid(),
    "created_at" timestamp with time zone not null default now(),

here created_at have default as now()

Expected behavior

I was expecting it to auto at "created_at" and not throw an error

System information

• OS: [macOS]
• Version of supabase-js: [2.38.0]
• Version of Node.js: [v18.3.0]

[RentfireFounder]

Setting defaultToNull to false solved it for me

export const updateChecklistData = async (values: BlueprintChecklistInsert[]) => {
    const { error } = await supabase
        .getClient()
        .from('blueprint_checklist')
        .upsert(values, { onConflict: 'id', defaultToNull: false });
    if (error) {
        throw error;
    }
};

[leohanon]

This is actually a PostgREST thing, not a supabase-js issue.

When you do an UPSERT or an INSERT sending a single record, then any missing fields are filled in with the database's default values.

When you do an UPSERT or an INSERT sending a LIST of records, missing fields across ALL records get filled in with the database's default values.

HOWEVER, any fields that exist in some records but not others are by default ASSUMED to be intentionally set to NULL. To change this you have to set "defaultToNull" = false.

@RentfireFounder - In your example, you send along two records, one where you define "created_at", and the other where you do NOT. The API thinks that it was left out intentionally and meant to be NULL, which then triggers an error since you defined the "created_at" field as being not nullable in your database.

All that said, it WOULD be helpful to improve the supabase documentation a bit, at a minimum to note that "defaultToNull" defaults to TRUE.