Zero-Downtime PostgreSQL Upsert with Audit Timestamp

1
2
3
4
5
6
7
8
9
10
INSERT INTO user_profiles (id, username, email, preferences, updated_at, version)
VALUES ($1, $2, $3, $4::jsonb, NOW(), 1)
ON CONFLICT (id) DO UPDATE
SET
  email = EXCLUDED.email,
  preferences = user_profiles.preferences || EXCLUDED.preferences,
  updated_at = NOW(),
  version = user_profiles.version + 1
WHERE user_profiles.version = EXCLUDED.version - 1
RETURNING id, username, email, updated_at, version;