Bulk Insert heterogenous data (missing keys w/ Default values vs present keys w/ None)

[henry2man]

Bug report

Describe the bug

In a bulk insert (and presumably in an update / upsert, too) we cannot mix different keys in the objects we are trying to insert. This is related with default values topic (setting the "missing" keys here will override any default value on the DB side).

It looks we're facing the same issue as the JS client: supabase/postgrest-js#173 (solved with supabase/postgrest-js#417)

I'm not an expert here, but it looks that we can do something with Prefer: missing=default (sources https://postgrest.org/en/v12/references/api/tables_views.html#bulk-insert-with-default-values / https://github.com/supabase/postgrest-js/pull/417/files#r1268323734)

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

create table foo (
  id bigint generated by default as identity primary key
, bar text
, baz int default 100
);

response = (
  supabase.client.table('foo')
    .insert([
      {
         "bar": "val1"
      },
      { 
        "bar": "val2",
        "baz": 15
      }
    ],
    # missing=PreferMissing.default,
  )
  ).execute()
)

Expected behavior

Data are inserted, and default values are applied.

Screenshots

N/A

System information

• OS: macOS Sonoma
• Version of supabase python client: 2.3.5

Additional context

Inspired by https://github.com/supabase/postgrest-js/pull/417/files I've done a fork with an additional parameter "missing: PreferMissing.default/null" (https://github.com/henry2man/postgrest-py).

But, sadly, it looks that Supabase is still rejecting my bulk inserts with mixed keys 😕

[henry2man]

Inspired by https://github.com/supabase/postgrest-js/pull/417/files I've done a fork with an additional parameter "missing: PreferMissing.default/null" (https://github.com/henry2man/postgrest-py).

But, sadly, it looks that Supabase is still rejecting my bulk inserts with mixed keys 😕

Here it is the added code: master...henry2man:postgrest-py:master

[henry2man]

I'm double checking that proper headers are being set with my improvements.

self.headers -> Headers({'prefer': 'return=representation,missing=default'})

[sapphire008]

Javascript and Dart client both have defaultToNull boolean parameter.

defaultToNull
Optional
boolean
Make missing fields default to null. Otherwise, use the default value for the column. This only applies when inserting new rows, not when merging with existing rows under ignoreDuplicates: false. This also only applies when doing bulk upserts.

https://supabase.com/docs/reference/javascript/upsert

which seems to set the header field Prefer as described by @henry2man. This may be a missing feature in the Python client.

[silentworks]

A fix was merged and released in version 0.16.3.