https://supabase.com/ logo
Join Discord
Powered by
I need a hint on how to take some data like `[{id:...
# help
h
I need a hint on how to take some data like 
[{id:<known id>, field:<value>}]
and put it into the database that has columns 
id, field, field1, field2
. Doing it with 
update
seems to require n commands, but with 
upsert
I seem to need the full record rather than just the field that is changing
n
Hello ! This thread has been automatically created from your message in a ``few seconds ago``. Pinging @User so that they see this as well! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the 
/title
command! We have solved your problem? Click the button below to archive it.
h
this is the error I get with 
upsert
'null value in column "field1" of relation "contestants" violates not-null constraint'
a
> Doing it with update seems to require n commands? You mean n commands for n rows?
n
hotbelgo (2022-03-27)
h
yes, correct
n
partially update rows
t
I wonder if you will be able to use 
coalesce
to your advantage Something like
Copy code
sql
INSERT INTO line_items AS li (
id,
      created,  -- If conflicts it will use what is there.
      modified,
    field1,
    field2
)
VALUES (1,now(),now(), 2,3)
  ON CONFLICT (id) DO UPDATE
  SET
    -- voucher_number = ali.voucher_number,
    created = new_line_items.created,
    modified = EXCLUDED.modified.
field1 = COALESE(EXCLUDED.field1, li.field1),
field2 = COALESE(EXCLUDED.field2, li.field2)
I just created a minimal example to test out my theory and it seems to work.
Copy code
sql
drop table line_items;
create table line_items (
id int UNIQUE,
created timestamp,
modified timestamp,
field1 text,
field2 text
);

INSERT INTO line_items AS li (
id,
      created,  -- If conflicts it will use what is there.
      modified,
    field1,
    field2
)
VALUES (1,now(),now(), '2', '3')
  ON CONFLICT (id) DO UPDATE
  SET
    created = li.created,
    modified = EXCLUDED.modified,
field1 = coalesce(EXCLUDED.field1, li.field1),
field2 = coalesce(EXCLUDED.field2, li.field2);

select * from line_items;
id|created |modified |field1|field2| --|-----------------------|-----------------------|------|------| 1|2022-03-27 18:27:49.988|2022-03-27 18:27:49.988|2 |3 |
Copy code
sql
INSERT INTO line_items AS li (
id,
      created,  -- If conflicts it will use what is there.
      modified,
    field1
    --field2
)
VALUES (1,now(),now(), '4')  -- removed field2
  ON CONFLICT (id) DO UPDATE
  SET
    created = li.created,
    modified = EXCLUDED.modified,
field1 = coalesce(EXCLUDED.field1, li.field1),
field2 = coalesce(EXCLUDED.field2, li.field2);
id|created |modified |field1|field2| --|-----------------------|-----------------------|------|------| 1|2022-03-27 18:27:49.988|2022-03-27 18:28:31.340|4 |3 |
h
wow - that's really cool
i had really hoped there was something simple in the SDK but I guess the suggestion is that there is not
t
> I guess the suggestion is that there is not I'm familiar with some of the underlying tech superbases uses with postgrest and postgres though not much supabase itself yet. So there may be a nicer way.
I can tell you it will be rather straight forward to add this into a 
instead of insert
trigger though.
If you take this approach you will be able to just insert into the view as you would normally a table and it will be an automatic upsert as it uses the trigger.
Copy code
sql
create view vline_items as select * from line_items;

CREATE OR REPLACE FUNCTION insert_line_items() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
begin

INSERT INTO line_items AS li (
id,
      created,  -- If conflicts it will use what is there.
      modified,
    field1,
    field2
)
VALUES (new.id, new.created, new.modified, new.field1, new.field2)
  ON CONFLICT (id) DO UPDATE
  SET
    created = li.created,
    modified = EXCLUDED.modified,
field1 = coalesce(EXCLUDED.field1, li.field1),
field2 = coalesce(EXCLUDED.field2, li.field2)
returning * into NEW;
    RETURN NEW;
END
$$;

CREATE TRIGGER insert_line_items_trigger
    INSTEAD OF INSERT
    on vline_items
    FOR EACH ROW
EXECUTE PROCEDURE insert_line_items();
 


INSERT INTO vline_items AS li (
id,
      created,  -- If conflicts it will use what is there.
      modified,
    field1,
    field2
)
VALUES (1,now(),now(), '4', '6')  --
 

INSERT INTO vline_items AS li (
id,
      created,  -- If conflicts it will use what is there.
      modified,
    field1
    --field2
)
VALUES (1,now(),now(), '7')  --
I'd be interested if there is a simpler solution perhaps already build into postgrest so you don't need to go down that path.
h
I had basically imagined there would be a "partial replace" in the JS SDK
a
The supabase client 
update
method supports partial updates right?
h
yes, but not with an array of multiple records to update
a
So you want something like an 
updateMany
method
h
I seem to have fallen into a gap between upsert and update
yes
this is just a toy app with small amounts of data, so I can do the full upsert
and perhaps if I were doing a big app, I wuld know SQL as well
a
I don't think there is a way to update many rows at once at the moment, it might be a good feature request though
h
👍
a
So supabase REST apis work through postgrest. I don't see any explicit mention about upsert needing all columns to be specified when used through the POST endpoint. https://postgrest.org/en/stable/api.html#upsert
Could you be sending null values in the code? Or maybe the API doc doesn't mention that, or maybe supabase uses a different endpoint
h
I recall that the error said I was sending nulls, but I those were being created by the SDV, as I was just coding the 'partial' update objects
n
Thread was archived by . Anyone can send a message to unarchive it.
PreviousNext
Powered by