Upsert with new and updated rows

[dcr8898]

Can I use insert_conflict and multi_insert to add new rows and update existing rows at once?

It seems to me that the requirement that all hashes passed to multi_insert have the same keys is preventing this use case, since updated rows have an id and new rows don't.

Assumptions

• I have a table, authors.
• With two columns, id and name.
• id is the primary key.

insert_conflict options

.insert_conflict(target: :id, update: {name: Sequel[:excluded][:name]})

multi_insert scenarios and results

If I create an Array of Hashes like so:

values = [{name: "John Doe"}, {name: "Joe Schmoe"}]

And chain the following to the above insert_conflict method :

.multi_insert(values)

It will work and create two records.

If I do the same with the following values:

values = [{id: 1, name: "Jane Doe"}]

The first record will be updated accordingly.

However, if I try the same method with the following values:

values = [
  {id: 1, name: "Jane Roe"},
  {name: "Bob Dole"},
]

It will fail with a NotNullViolation, stating that "null value in column 'id' violates not-null constraint," when processing the second Hash.

Solution?

Is there a simple solution for this use case?

Based on my research, I could filter the new records and add an :id key that invokes a Postgres function to retrieve the next id in the table's primary key sequence to set its value. In this way, all hashes would have an :id key. But this feels really clunky and I'm not sure if it would cause problems down the road. I haven't tried this, but the SQL could look something like this, I think:

CREATE SEQUENCE authors_id_seq;

INSERT INTO authors (id, name)
VALUES
  (1, 'Jane Roe'),
  (nextval('authors_id_seq'), 'Bob Dole')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name;

Even if this works, I haven't thought through how to do this in Sequel yet.

It occurs to me that this whole issue may be a constraint of SQL itself. If so, the only other strategy I can think of is to partition the values Array into new row hashes and updated row hashes and operate on them separately. I believe I can do this with the very same insert_conflict method, as long as I pass the two groups separately. But I am curious if there is a way to do it all in one go.

Thank you!

Thank you kindly for reading this, and for any advice you can offer! 🤓

[jeremyevans]

It's invalid to call multi_insert if not all hashes have the same keys. You can produce the SQL you want with Sequel:

DB[:authors].
  insert_conflict(target: :id, update: {name: Sequel[:excluded][:name]}).
  multi_insert([{id: 1, name: 'Jane Roe'}, {id: Sequel.function(:nextval, 'authors_id_seq'), name: 'Bob Dole'}])

[dcr8898]

Thank you for the ridiculously fast response! 💨

I think my gears were slowly grinding in that direction. 🤓 Now I have to decide if I want to code Postgres sequence names into my app, or split the operation into two queries (what I implemented so far).

Thank you! 💚

[dcr8898]

I wanted to circle back for future readers with another solution to this problem. In most instances where your id column is a SERIAL or IDENTITY, you can also use the Postgres keyword DEFAULT as a dummy id value to prompt Postgres to supply a value for id.

This is likely easier, since you don't need to reference the name of a Postgres sequence in your code.

This is working for me:

DB[:authors].
  insert_conflict(target: :id, update: {name: Sequel[:excluded][:name]}).
  multi_insert([{id: 1, name: 'Jane Roe'}, {id: Sequel.lit('DEFAULT'), name: 'Bob Dole'}])