Katkat
Back

Write PostgreSQL updates with date filters

CircleSoftware Engineer · Online Assessment
MediumSoftware EngineerOnline AssessmentData Manipulation (SQL/Python)

Last updated

This question evaluates competency in PostgreSQL data manipulation, covering updates with correct timestamp handling, upserts with conflict resolution, date-range selection using timestamptz, and index design plus performance analysis with EXPLAIN ANALYZE.

Write PostgreSQL statements to: (

  1. update a flight's price and set updated_at correctly; (
  2. upsert a booking record with conflict handling; (
  3. select bookings filtered by a UTC date range using timestamptz without off-by-one errors; (
  4. add appropriate indexes and demonstrate their use with EXPLAIN ANALYZE. Discuss common pitfalls when moving from generic SQL to PostgreSQL, including date/time casting, time zones, text vs citext, and use of immutable/stable functions in indexes.