hotbelgo
03/27/2022, 6:47 AM[{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 changingNeedle
03/27/2022, 6:47 AM/title
command!
We have solved your problem?
Click the button below to archive it.hotbelgo
03/27/2022, 6:49 AMupsert
'null value in column "field1" of relation "contestants" violates not-null constraint'
Avin
03/27/2022, 7:00 AMNeedle
03/27/2022, 7:00 AMhotbelgo
03/27/2022, 7:12 AMNeedle
03/27/2022, 7:13 AMtourdownunder
03/27/2022, 7:18 AMcoalesce
to your advantage
Something like
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)
tourdownunder
03/27/2022, 7:29 AMsql
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 |
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 |hotbelgo
03/27/2022, 7:40 AMhotbelgo
03/27/2022, 7:40 AMtourdownunder
03/27/2022, 8:04 AMtourdownunder
03/27/2022, 8:07 AMinstead of insert
trigger though.tourdownunder
03/27/2022, 8:17 AMsql
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.hotbelgo
03/27/2022, 8:25 AMAvin
03/27/2022, 8:25 AMupdate
method supports partial updates right?hotbelgo
03/27/2022, 8:25 AMAvin
03/27/2022, 8:26 AMupdateMany
methodhotbelgo
03/27/2022, 8:26 AMhotbelgo
03/27/2022, 8:26 AMhotbelgo
03/27/2022, 8:26 AMhotbelgo
03/27/2022, 8:27 AMAvin
03/27/2022, 8:28 AMhotbelgo
03/27/2022, 8:32 AMAvin
03/27/2022, 8:34 AMAvin
03/27/2022, 9:07 AMhotbelgo
03/27/2022, 9:45 AMNeedle
03/27/2022, 9:45 AM