Determine Table Insertion into Database: Write SQL to Detect Whether a Row Already Exists Before Insert
Interview Experience
Problem
Given a users table, write a SQL statement that inserts a new user only if no user with the same email already exists.
Return the resulting row whether it was newly inserted or already present.
sql
-- Table: users
-- id SERIAL PRIMARY KEY
-- email TEXT UNIQUE
-- name TEXT
-- created_at TIMESTAMP DEFAULT NOW()
Task 1: Write an INSERT ... ON CONFLICT DO NOTHING statement and then select the row.
Task 2: Write a single upsert statement using INSERT ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name and explain when you would prefer one over the other.
Example:
sql
-- Before: users has (1, '[email protected]', 'Alice')
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alicia')
ON CONFLICT (email) DO NOTHING;
--
**Result** row unchanged, no new row inserted
Follow-ups
1. What is the difference between DO NOTHING and DO UPDATE semantics? Give a concrete use case for each.
2. Is INSERT ... ON CONFLICT atomic? What isolation level guarantees does it provide?
3. How do you handle bulk upserts (1,000 rows) efficiently?
4. Rewrite the logic in Python using SQLAlchemy ORM without raw SQL.
Full Details
Problem
Given a users table, write a SQL statement that inserts a new user only if no user with the same email already exists.
Return the resulting row whether it was newly inserted or already present.
sql
-- Table: users
-- id SERIAL PRIMARY KEY
-- email TEXT UNIQUE
-- name TEXT
-- created_at TIMESTAMP DEFAULT NOW()
Task 1: Write an INSERT ... ON CONFLICT DO NOTHING statement and then select the row.
Task 2: Write a single upsert statement using INSERT ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name and explain when you would prefer one over the other.
Example:
sql
-- Before: users has (1, '[email protected]', 'Alice')
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alicia')
ON CONFLICT (email) DO NOTHING;
--
**Result** row unchanged, no new row inserted
Follow-ups
1. What is the difference between DO NOTHING and DO UPDATE semantics? Give a concrete use case for each.
2. Is INSERT ... ON CONFLICT atomic? What isolation level guarantees does it provide?
3. How do you handle bulk upserts (1,000 rows) efficiently?
4. Rewrite the logic in Python using SQLAlchemy ORM without raw SQL.
About This Question
This is a candidate experience report from a chime interview during the phone round.