7

I have been looking for a solution to this problem since couple of hours now. It seems very simple but for some reason I can't get it to work.

I have a model, lets call it MyModel. It has 3 columns, :id, :column_a, :relationship_id

:relationship_id is a foreign key with not null constraint.

I have 4 records in the database already like such:-

:id, :column_a, :relationship_id,
1, 1, 7
2, 2, 8
3, 3, 9
4, 4, 10

I want to change the values of :column_a, using the following data

data = [{"id"=>"1", "column_a"=>4},
        {"id"=>"2", "column_a"=>3},
        {"id"=>"3", "column_a"=>2},
        {"id"=>"4", "column_a"=>1}]

But this data is missing :relationship_id column values which I want to keep unchanged.

When I do something like MyModel.upsert_all(data, update_only: :column_a) I get the following error

ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR:  null value in column "relationship_id" of relation "my_models" violates not-null constraint
DETAIL:  Failing row contains (1, 4, null).

I have tried update_all and raw SQL but the problem remains the same. The only way I can get it working is by individually updating each record which is not efficient.

Maybe I am doing something wrong? Any ideas?

Improve this question
6
  • "upsert" means attempt an insert and if there's a conflict then update the existing row instead. You can't attempt to do the insert portion of the upsert if you aren't passing all the required values. Are you able to pass a relationship_id value in your data? Commented Nov 29, 2022 at 2:05
  • Yes I understand that. Unfortunately getting relationship_id will be a difficult and inefficient solution. I wonder why there is no way to bulk update only specific columns :/ Commented Nov 29, 2022 at 7:00
  • 1
    Can't you just do one more query before you do the update with MyModel.where(id: data.map { |row| row['id'] }).pluck(:id, :relationship_id).to_h and then add the relationship_id values into your data array? Commented Nov 29, 2022 at 14:06
  • Yes. I am implementing this. By far this looks like the only solution Commented Nov 29, 2022 at 14:07
  • Yeah, otherwise, if there's no pattern to updates you want to make then I think the only option would be to try to build raw SQL that does something like the answers to this question. Commented Nov 29, 2022 at 14:16
Related questions
8
Bulk upsert with Ruby on Rails
24
Rails Migration changing column to use Postgres arrays
1
Rails Handle Join Table UniqueViolation
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.