Answer a question

I'm trying to use the upsert_all method to update and insert all records (from ActiveRecord models) at the same time in my Postgres database. However, I come across a problem.
I first fetch existing records and update them, that makes sense as the record ID (which is my primary key) is needed to update it using the upsert_all method.
For new records though, I don't have an ID, but have to set one as upsert_all requires to have the same keys for all records, so I must set the ID, and I though setting it to nil might work. When doing this, I get the following error: PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint

So how am I supposed to use the upsert_all method in Rails?
Is there a way to specify that if the ID is nil, the value is bypassed and auto-incremented by postgres?

Answers

The example from the rails documentation should tell you everything you need to know:

# Inserts multiple records, performing an upsert when records have duplicate ISBNs.
# Here "Eloquent Ruby" overwrites "Rework" because its ISBN is duplicate.

Book.upsert_all([
  { title: "Rework", author: "David", isbn: "1" },
  { title: "Eloquent Ruby", author: "Russ", isbn: "1" }
], unique_by: :isbn)

Book.find_by(isbn: "1").title # => "Eloquent Ruby"

So ... no, you don't need to know the IDs for the records. How can you know the IDs for records you haven't created yet? But, you should guide the upsert_all method on how to identify which record to update by passing unique_by.

Another example of this might be updating a bunch of users. Yes they'll all have a ID in the database, but they should also all have a unique email or username or something:

User.upsert_all([
  { email: "bob@acme.com", role: "manager" },
  { email: "new-user@example.com", role: "customer" }
], unique_by: :email)

In this example, bob@acme.com already existed, but new-user@example.com did not. Bob had his role updated, and new-user had their account created.