Bulk Upsert through PostgreSQL 9.5+
Warning: This only works for PostgreSQL 9.5+
Given the following:
a users table:
users table only has 1 user with values:
- id: 1
- name: Alejandro
- created_at: '2010-10-10 10:00:00.000000'
- updated_at: '2010-10-10 10:00:00.000000'
You can upsert using the following SQL query:
INSERT INTO users(id, name, created_at, updated_at) VALUES (1, 'Alexander', NOW(), NOW()), (2, 'Belle', NOW(), NOW()) ON CONFLICT (id) DO UPDATE SET name=EXCLUDED.name, updated_at=NOW() ;
- User(id=1) will be renamed from Alejandro to Alexander. The updated_at value will be set to current time.
- User(id=2) will be inserted to users table with name = Belle, and both created_at and updated_at will be set to current time.