Postgresql medium
What is the recommended approach for performing an 'Upsert' (Insert or Update on conflict) in modern PostgreSQL versions?
- A REPLACE INTO: Replaces the existing record with the new values if a conflict is detected.
- B UPSERT SET: A dedicated keyword for atomic synchronization of single-row record updates.
- C INSERT ... ON CONFLICT DO UPDATE: The native Postgres syntax for conflict-aware writes.
- D SYNCHRONIZE TABLE: A bulk utility command for merging source and target data partitions.
Answer: C. INSERT ... ON CONFLICT DO UPDATE: The native Postgres syntax for conflict-aware writes.
`ON CONFLICT DO UPDATE` is the most common way to handle upserts in Postgres. Starting with PG 15, the SQL-standard `MERGE` command is also supported, giving developers more standardized options for complex data flows.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In a PostgreSQL environment, what functional characteristic distinguishes a `MATERIALIZED VIEW` from a standard `VIEW`?
- Which standard PostgreSQL utility is designed to extract a single database into a script or archive file for backup purposes?
- Why would PostgreSQL reject a `DELETE` operation on a record despite the user having full administrative privileges?
- The 'Heap Only Tuples' (HOT) optimization improves UPDATE performance. Which specific set of conditions must be met for a HOT update to occur?
- Which native command-line utility is used to execute SQL queries and manage PostgreSQL databases from a terminal session?
- What is the specific purpose of 'Freezing' a row (modifying its `xmin` status) during a major VACUUM operation?