+1 for this. I mean, I thought that is what upsert is for.

I have multiple rows…if I add a new one, it doesn’t have an ID yet…so, I would expect existing items get updated, while the new one, gets inserted.

I would like to know about this myself since I’m trying to upsert.

In my situation I can have references to the ids of the rows but in the same operation I also need to add new rows of which I do not have an identifier.

This returns {"message":"All object keys must match"}

summing up the upsert does not do what I expect that is to add new rows and update those already in db in the same operation.

this forces me to have to make two separate api calls for insert and update

any update for this?

been quite for a while now.

I would like to know about this myself since I’m trying to upsert.

In my situation I can have references to the ids of the rows but in the same operation I also need to add new rows of which I do not have an identifier.

This returns {"message":"All object keys must match"}

summing up the upsert does not do what I expect that is to add new rows and update those already in db in the same operation.

this forces me to have to make two separate api calls for insert and update

+1 for allowing this pattern

@ftonato yep, I know that the equivalent SQL wouldn’t work, but that’s kind of is the point of Supabase for me: an API wrapper around Postgres, which simplifies the hosting, design, as well as querying the DB.

I don’t see any disadvantages to default missing / mismatching keys to null, except a tiny performance penalty due to checking all objects’ keys before sending to server. I’m sure that something like Sequelize allow it.

If you still think there is some negative side effect or tradeoff, maybe you could at least make the error message more descriptive.

FYI, this is being worked at https://github.com/PostgREST/postgrest/pull/2672.

It feels like it should at least allow the upsert with missmatching keys when a default value is provided for the missmatched keys in the table.

So that upsert like this is possible: { field1: 'something' field2: 'something' }, { field1: 'something', field2: undefined // default value provided or null authorised on table. }

…bumped into this error, you guys saved my day. I can add the so-called missing fields into my table of objects to send for upsert, but… doesnt seem intuitive. I hope this gets fixed anytime soon

I also need to add new rows of which I do not have an identifier.

A workaround for making null identifiers work is to create a trigger that converts the null to the default id. Like:

-- Change items and its sequence according to your table
CREATE OR REPLACE FUNCTION items_null_id_is_default() RETURNS TRIGGER AS $$
BEGIN
  NEW.id = coalesce(NEW.id, nextval('items_id_seq'));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER items_null_id_is_default 
BEFORE INSERT ON items FOR EACH ROW EXECUTE PROCEDURE items_null_id_is_default();

Then you can avoid the All object keys must match error by adding a null id on the rows for which you don’t have an identifier:

[
    {
      id: null,
      name: 'a name'
    },
    {
      id: 123,
      name: 'another name'        
    }
]

(upsert should work then).

Once https://github.com/PostgREST/postgrest/issues/1567 is cleared out then the library will allow this out-of-the-box.

Hello @peachp,

We can treat this as a bug, but it must be said that in SQL this expression is also not valid.

INSERT INTO TABLE_NAME (column_a,
                        column_b)
VALUES ('aaa'),
       ('aaa', 'bbb');

Error: Run query failed (INSERT has more target columns than expressions)