About on_duplicate_key_update with create or update behavior

[berniechiu]

Hi, sorry to bother with an issue, I have a question about on_duplicate_key_update

I have a generated SQL like this in Postgres using the gem

INSERT INTO "products" ("id","identifier","name","length","width","height","weight","item_category_id","input_type","created_at","updated_at")
VALUES 
  ('c76bbcb6-6ec7-454f-8bb2-b35eeaa7df62','B0000DE5931','occaecati','5.0','76.0','10.0','8.0',14,'csv_upload','2019-12-02 15:00:34.836113','2019-12-02 15:00:34.836113'),
  (NULL,'B000A3T3LY2','quia','25','13','64','1',19,'csv_upload','2019-12-02 15:00:34.836113','2019-12-02 15:00:34.836113')
  ON CONFLICT (id) DO UPDATE SET "identifier"=EXCLUDED."identifier","name"=EXCLUDED."name","length"=EXCLUDED."length","width"=EXCLUDED."width","height"=EXCLUDED."height","weight"=EXCLUDED."weight","item_category_id"=EXCLUDED."item_category_id","updated_at"=EXCLUDED."updated_at" RETURNING "id"

And then I got this error

ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR:  null value in column "id" violates not-null constraint

I imagined it'll update the record if the id is found or else it creates a new one, but apparently it's not what I thought. I can separate the query of course, but I imagined the purpose of
on_duplicate_key_update is to create or update, I wonder it there's an easier way to still write it into one SQL statement or I'm using it wrong. @@

[jkowens]

When doing an insert, you cannot mix records that do not have an id set with those that do because the resulting sql statement will be invalid as you are experiencing. The on_duplicate_key_update option will update existing records instead of the database throwing a duplicate key exception.

[berniechiu]

Thanks a lot!