Upsert (SQL)
The concept of upsert in postgres....It's something like...
*Inserting* when the data is novel to the system & *Updating* when the data is already in the system (but how do it know?)
In Postgres, UPSERT is performed via INSERT...
But How Do It Know?
we must specify with *ON CONFLICT*
INSERT INTO tasks (name, priority) VALUES ('Respond', 3) ON CONFLICT DO NOTHING
If there is a unique constraint on name, and 'Respond' already exists in the database, nothing will happen.
But what if we want to update the 'Respond' row?
INSERT INTO tasks (name, priority) VALUES ('Respond', 300) ON CONFLICT (name) DO UPDATE SET priority = EXCLUDED.priority
See here, that the column name must be specified.
BONUS: You can call functions to unite the original row value with the incoming row value. Let's look at it
INSERT INTO tasks (name, priority, tags_array) VALUES ('Respond', 3, ARRAY['urgent']) ON CONFLICT DO UPDATE SET priority = EXCLUDED.priority tags = array_cat(EXCLUDED.tags, tasks.tags)
But what about trying to do this seemingly basic task in the twisted clojure honeysql syntax...
It cannot be as easy as this, can it?
Hmmm.....yeah it doesn't look too ****** up, I guess...
So, we have access to a function called (hdb/upsert-values-set-excluded) and another function called (hdb/upsert-values-do-nothing)
Let's start with the easier one I guess:
(hdb/upsert-values-do-nothing customer-schema/tasks [{:name "Respond" :priority 3] [:name] {:tracking? false})
Right, so it's a vector of values...that makes sense. You specify the "ON CONFLICT" columns in the third argument. Okay, sure. The tracking? map appears to be an option that specifies whether or not we pass auditing information to the database. OKAY, I UNDERSTAND EVERYTHING. If there is already a database row with :name value provided, then the row will be left alone, no update at all.
Looks like it's the same deal for upsert-values-set-excluded. For some reason, "set-excluded" confused me in the past. It still seems like a bad name for the function. I think I would have called it upsert-values-set-on-conflict, but that's my brain and I am a young old man.
Let's say we have a unique constraint on two columns and we want to upsert on them...
(hdb/upsert-values-set-excluded customer-schema/cars [{:make "Chevrolet" :model "Prizm" :rating 2000}] [:make :model])
If Chevrolet Prizm already exists in the database, the row will be updated with the new rating. It looks like our codebase doesn't have built-in ability to make function calls upon SETTING values.
Future topics I'd like to understand:
Async Clojure
Websockets
React.js Advanced Concepts













