Part 49: The Upsert Pattern in PostgreSQL: ON CONFLICT DO UPDATE

PostgreSQL provides a versatile and standard upsert implementation.

1. PostgreSQL Syntax

INSERT INTO user_preferences (user_id, theme, language)
VALUES (101, 'dark', 'en')
ON CONFLICT (user_id) 
DO UPDATE SET 
  theme = EXCLUDED.theme,
  language = EXCLUDED.language,
  updated_at = CURRENT_TIMESTAMP;

The EXCLUDED Pseudo-Table: Represents the values proposed in the VALUES clause that conflicted with existing rows.

Key Takeaways

• Specify the conflicting unique column in ON CONFLICT (col).
• Use EXCLUDED.column to reference incoming values.

In Part 50, we explore The ANSI SQL MERGE Statement!