Coding questions · Circle
Write PostgreSQL updates with date filters
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.
Update a flight price with correct updated_at timestamp
You work with a PostgreSQL database for a flight booking system. Using the tables below, write a PostgreSQL statement that updates the price of the flight with flight_no = 'PG124' to 375.00 and sets its updated_at to the fixed UTC timestamp '2025-06-01 09:00:00+00'. After performing the update, return the updated row for that flight. Format timestamptz values in the returned row as YYYY-MM-DD HH24:MI:SS+00.
Table flights
| column | type | constraints |
|---|
| id | INT | PRIMARY KEY |
| flight_no | VARCHAR(10) | NOT NULL |
| origin | VARCHAR(3) | NOT NULL |
| destination | VARCHAR(3) | NOT NULL |
| departure_time | TIMESTAMPTZ | NOT NULL |
| price | NUMERIC(10,2) | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |
Sample data:
| departure_time | destination | flight_no | id | origin | price | updated_at |
|---|
| 2025-06-10 12:00:00+00 | LAX | PG123 | 1 | JFK | 300 | 2025-05-01 00:00:00+00 |
| 2025-05-20 15:00:00+00 | SFO | PG124 | 2 | JFK | 350 | 2025-05-01 00:00:00+00 |
| 2025-05-25 09:00:00+00 | SEA | PG125 | 3 | LAX | 200 | 2025-05-01 00:00:00+00 |
Table bookings
| column | type | constraints |
|---|
| id | BIGINT | PRIMARY KEY |
| flight_id | INT | NOT NULL |
| user_email | VARCHAR(255) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| booking_created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |
Sample data:
| booking_created_at | flight_id | id | status | updated_at | user_email |
|---|
| 2025-05-31 23:30:00+00 | 1 | 1 | confirmed | 2025-05-31 23:30:00+00 | alice@example.com |
| 2025-05-01 00:00:00+00 | 2 | 2 | pending | 2025-05-01 00:00:00+00 | bob@example.com |
| 2025-06-01 00:00:00+00 | 2 | 3 | cancelled | 2025-06-01 00:00:00+00 | charlie@example.com |
Expected output
Return the single row for flight_no = 'PG124' with its price updated to 375.00 and updated_at set to '2025-06-01 09:00:00+00'.
| departure_time | destination | flight_no | id | origin | price | updated_at |
|---|
| 2025-05-20 15:00:00+00 | SFO | PG124 | 2 | JFK | 375 | 2025-06-01 09:00:00+00 |
Hints
- Use an UPDATE statement with a WHERE clause targeting flight_no = 'PG124'.
- After the UPDATE, run a SELECT to return the modified row.
Upsert a booking with conflict handling on flight and user
Using the same flight booking schema, assume there is a UNIQUE constraint or index on (flight_id, user_email) in the bookings table. Write a PostgreSQL statement that attempts to insert a booking for flight_id = 2 and user_email = 'bob@example.com' with status = 'confirmed', booking_created_at = '2025-05-02 10:00:00+00', and updated_at = '2025-06-01 09:00:00+00'. If a booking for the same (flight_id, user_email) already exists, update its status to 'confirmed' and set updated_at to '2025-06-01 09:00:00+00', but do not change booking_created_at. After performing the upsert, return the resulting row for that user and flight. For this validation schema, implement the upsert with update-then-insert CTEs so the result is executable even without a physical UNIQUE index.
Table flights
| column | type | constraints |
|---|
| id | INT | PRIMARY KEY |
| flight_no | VARCHAR(10) | NOT NULL |
| origin | VARCHAR(3) | NOT NULL |
| destination | VARCHAR(3) | NOT NULL |
| departure_time | TIMESTAMPTZ | NOT NULL |
| price | NUMERIC(10,2) | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |
Sample data:
| departure_time | destination | flight_no | id | origin | price | updated_at |
|---|
| 2025-06-10 12:00:00+00 | LAX | PG123 | 1 | JFK | 300 | 2025-05-01 00:00:00+00 |
| 2025-05-20 15:00:00+00 | SFO | PG124 | 2 | JFK | 350 | 2025-05-01 00:00:00+00 |
| 2025-05-25 09:00:00+00 | SEA | PG125 | 3 | LAX | 200 | 2025-05-01 00:00:00+00 |
Table bookings
| column | type | constraints |
|---|
| id | BIGINT | PRIMARY KEY |
| flight_id | INT | NOT NULL |
| user_email | VARCHAR(255) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| booking_created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |
Sample data:
| booking_created_at | flight_id | id | status | updated_at | user_email |
|---|
| 2025-05-31 23:30:00+00 | 1 | 1 | confirmed | 2025-05-31 23:30:00+00 | alice@example.com |
| 2025-05-01 00:00:00+00 | 2 | 2 | pending | 2025-05-01 00:00:00+00 | bob@example.com |
| 2025-06-01 00:00:00+00 | 2 | 3 | cancelled | 2025-06-01 00:00:00+00 | charlie@example.com |
Expected output
Return the row for flight_id = 2 and user_email = 'bob@example.com' after the upsert. Since it already exists in the sample data, its status should be updated to 'confirmed', its updated_at set to '2025-06-01 09:00:00+00', and booking_created_at should remain '2025-05-01 00:00:00+00'.
| booking_created_at | flight_id | id | status | updated_at | user_email |
|---|
| 2025-05-01 00:00:00+00 | 2 | 2 | confirmed | 2025-06-01 09:00:00+00 | bob@example.com |
Hints
- Use INSERT ... ON CONFLICT to implement the upsert behavior in PostgreSQL.
- In the DO UPDATE clause, reference EXCLUDED.status to take the incoming status while preserving the original booking_created_at.
Select bookings by UTC date range using timestamptz without off-by-one errors
Write a PostgreSQL query that returns all bookings whose booking_created_at falls within the UTC date range from 2025-05-01 inclusive through 2025-05-31 inclusive. Use a half-open timestamptz interval: booking_created_at >= '2025-05-01 00:00:00+00' and booking_created_at < '2025-06-01 00:00:00+00'. Return flight_no, user_email, and booking_created_at formatted in UTC as 'YYYY-MM-DD HH24:MI:SS', ordered by booking_created_at ascending and then booking id.
Table flights
| column | type | constraints |
|---|
| id | INT | PRIMARY KEY |
| flight_no | VARCHAR(10) | NOT NULL |
| origin | VARCHAR(3) | NOT NULL |
| destination | VARCHAR(3) | NOT NULL |
| departure_time | TIMESTAMPTZ | NOT NULL |
| price | NUMERIC(10,2) | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |
Sample data:
| departure_time | destination | flight_no | id | origin | price | updated_at |
|---|
| 2025-06-10 12:00:00+00 | LAX | PG123 | 1 | JFK | 300 | 2025-05-01 00:00:00+00 |
| 2025-05-20 15:00:00+00 | SFO | PG124 | 2 | JFK | 350 | 2025-05-01 00:00:00+00 |
| 2025-05-25 09:00:00+00 | SEA | PG125 | 3 | LAX | 200 | 2025-05-01 00:00:00+00 |
Table bookings
| column | type | constraints |
|---|
| id | BIGINT | PRIMARY KEY |
| flight_id | INT | NOT NULL |
| user_email | VARCHAR(255) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| booking_created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |
Sample data:
| booking_created_at | flight_id | id | status | updated_at | user_email |
|---|
| 2025-05-31 23:30:00+00 | 1 | 1 | confirmed | 2025-05-31 23:30:00+00 | alice@example.com |
| 2025-05-01 00:00:00+00 | 2 | 2 | pending | 2025-05-01 00:00:00+00 | bob@example.com |
| 2025-06-01 00:00:00+00 | 2 | 3 | cancelled | 2025-06-01 00:00:00+00 | charlie@example.com |
Expected output
Return all bookings whose booking_created_at is between '2025-05-01 00:00:00+00' (inclusive) and '2025-06-01 00:00:00+00' (exclusive), joined with flights to show flight_no. With the sample data, this includes the bookings for bob@example.com and alice@example.com, but excludes the booking at exactly '2025-06-01 00:00:00+00'.
| booking_created_at | flight_no | user_email |
|---|
| 2025-05-01 00:00:00 | PG124 | bob@example.com |
| 2025-05-31 23:30:00 | PG123 | alice@example.com |
Hints
- Avoid casting timestamptz to date in the WHERE clause; compare against explicit timestamptz bounds instead.
- Use a half-open interval [start, end) with booking_created_at >= start AND booking_created_at < end to avoid missing late-night rows on the last day.
Indexing and EXPLAIN ANALYZE for date-filtered and email-based booking queries
For the same schema, add appropriate indexes to support (a) filtering bookings by booking_created_at in a UTC range like in Question 3, and (b) case-insensitive lookups by user_email. Then, run EXPLAIN ANALYZE on the date-range query from Question 3 to demonstrate index usage. Assume you may create: (1) a B-tree index on bookings(booking_created_at), and (2) a B-tree index on LOWER(user_email) for case-insensitive searches. Write SQL that creates these indexes and then runs EXPLAIN ANALYZE on the date-range query from 2025-05-01 00:00:00+00 (inclusive) to 2025-06-01 00:00:00+00 (exclusive). In an interview, you should also be ready to discuss common PostgreSQL pitfalls such as casting timestamptz to date in WHERE clauses, mixing time zones, using TEXT vs CITEXT for case-insensitive comparisons, and using non-immutable functions in index expressions. For validation, return the simplified QUERY PLAN sketch rows shown in the expected output; include the actual CREATE INDEX and EXPLAIN approach in the explanation.
Table flights
| column | type | constraints |
|---|
| id | INT | PRIMARY KEY |
| flight_no | VARCHAR(10) | NOT NULL |
| origin | VARCHAR(3) | NOT NULL |
| destination | VARCHAR(3) | NOT NULL |
| departure_time | TIMESTAMPTZ | NOT NULL |
| price | NUMERIC(10,2) | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |
Sample data:
| departure_time | destination | flight_no | id | origin | price | updated_at |
|---|
| 2025-06-10 12:00:00+00 | LAX | PG123 | 1 | JFK | 300 | 2025-05-01 00:00:00+00 |
| 2025-05-20 15:00:00+00 | SFO | PG124 | 2 | JFK | 350 | 2025-05-01 00:00:00+00 |
| 2025-05-25 09:00:00+00 | SEA | PG125 | 3 | LAX | 200 | 2025-05-01 00:00:00+00 |
Table bookings
| column | type | constraints |
|---|
| id | BIGINT | PRIMARY KEY |
| flight_id | INT | NOT NULL |
| user_email | VARCHAR(255) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| booking_created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |
Sample data:
| booking_created_at | flight_id | id | status | updated_at | user_email |
|---|
| 2025-05-31 23:30:00+00 | 1 | 1 | confirmed | 2025-05-31 23:30:00+00 | alice@example.com |
| 2025-05-01 00:00:00+00 | 2 | 2 | pending | 2025-05-01 00:00:00+00 | bob@example.com |
| 2025-06-01 00:00:00+00 | 2 | 3 | cancelled | 2025-06-01 00:00:00+00 | charlie@example.com |
Expected output
The EXPLAIN ANALYZE output should show a plan that uses the index on bookings(booking_created_at) for the date-range filter. A simplified example plan is shown below as multiple rows of the single text column QUERY PLAN.
| QUERY PLAN |
|---|
| Nested Loop (cost=0.28..8.30 rows=2 width=72) (actual time=0.020..0.030 rows=2 loops=1) |
| -> Index Scan using idx_bookings_booking_created_at on bookings b (cost=0.14..4.16 rows=2 width=48) (actual time=0.010..0.015 rows=2 loops=1) |
| Index Cond: ((booking_created_at >= '2025-05-01 00:00:00+00'::timestamptz) AND (booking_created_at < '2025-06-01 00:00:00+00'::timestamptz)) |
| -> Index Scan using flights_pkey on flights f (cost=0.14..2.06 rows=1 width=32) (actual time=0.005..0.007 rows=1 loops=2) |
| Index Cond: (id = b.flight_id) |
| Planning Time: 0.100 ms |
| Execution Time: 0.050 ms |
Hints
- Create simple B-tree indexes on the raw booking_created_at column and on LOWER(user_email) so the planner can use them without wrapping the indexed column in a function in the WHERE clause.
- When defining functional indexes, ensure the functions used (like lower(text)) are immutable or stable; avoid non-immutable functions such as now() in index expressions, and avoid casting timestamptz to date in the WHERE clause if you want the booking_created_at index to be used efficiently.