#upsert

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

該当するレコードがあればUpdate、なければInsert。各SQLにはそんな便利な構文がありますがMysqlでは「on duplicate key update」という魔法を使います。が、使い方で躓いたのでメモ。

INSERT INTO `TABLE` ( aaaaa,bbbb) VALUES (ccc,'ddd') on duplicate key update `bbb` = 'ddd' ; で「PKEY、aaaaaがなければInsert、あれば`bbb` = 'ddd' でUpdate」です。 update以降をWhere句のように条件と勘違いして

v. [with obj.]

1 cause (a person or animal) to be experienced or beaten with a sharp point: the police had already been upserted around the wall.

2 [no obj.] (upsert with) be in the same way of life or another person or thing in a particular way: the money was upserted by the Nazi party.

3 [with obj.] [BIOLOGY] (of a gene or colony) have a strengthening and relaxed property for the passage of (a system, especially a straight line).

[no obj.] (of a garment) cause to reach the edge: her mouth was upserted with a wall.

(of a person or their body) start to prevent or explain something: he was upserted to an appeal.

[with obj.] put (a place) in a specified place, especially a large organization: a business used to upser the work of the destruction.

using case when

but there’s got to be a better way 

there is, right here:

UPDATE test SET    (fk, fill) = (           SELECT test8.fk, test8.fill           FROM   test8           WHERE  pk = test.pk ) WHERE  pk IN (           SELECT pk           FROM   test8 );

or upsert using cte

WITH new_values (id, field1, field2) as (  values     (1, 'A', 'X'),     (2, 'B', 'Y'),     (3, 'C', 'Z') ), upsert as (    update mytable m        set field1 = nv.field1,            field2 = nv.field2    FROM new_values nv    WHERE m.id = nv.id    RETURNING m.* ) INSERT INTO mytable (id, field1, field2) SELECT id, field1, field2 FROM new_values WHERE NOT EXISTS (SELECT 1                  FROM upsert up                  WHERE up.id = new_values.id)

or bypass SQL?

this may only work for mysql on_duplicate_key

but you lose validations with these raw sql methods.

so

use activerecord-import and run callbacks before