Thanks for posting this article !
I didn’t know about the ON CONFLICT and the RETURNING clause of PostgresSQL, but it’s so cool.
I played with it this morning and you can define a bulk upsert query so easily :)
For example, here is a “people” table with a unique constraint on a UUID field.
CREATE TABLE people (
id SERIAL PRIMARY KEY,
uuid UUID NOT NULL,
name VARCHAR(100) NOT NULL,
age INT NOT NULL,
UNIQUE(guid)
);
And here is a bulk-upsert query:
INSERT INTO people
("guid", "name", "age")
VALUES
('e3325f67-fedf-430a-b54b-9c99c7d9f7d7', 'Karl', 30),
('8d320d07-aa63-4cf9-9660-6be6e46c86be', 'Friedrich', 16),
('8318a017-77a3-4457-914c-8d47c4fdfe3b', 'Rosa', 30)
ON CONFLICT (guid)
DO UPDATE SET name = excluded.name, age = excluded.age
If a row fail to be inserted because its uuid already exists, Postgres will update the “name” and “age” field of this row.
Thanks for posting this article ! I didn’t know about the
ON CONFLICTand theRETURNINGclause of PostgresSQL, but it’s so cool.I played with it this morning and you can define a bulk upsert query so easily :)
For example, here is a “people” table with a unique constraint on a UUID field.
And here is a bulk-upsert query:
If a row fail to be inserted because its uuid already exists, Postgres will update the “name” and “age” field of this row.