TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 04

Data Types and Constraints: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONSTRAINTS & DML - CONCEPTUAL

  1. Q1What does PRIMARY KEY enforce - and what does it implicitly create?
  2. Q2Can a table have more than one PRIMARY KEY? Can it have a composite PK? Explain.
  3. Q3What does a FOREIGN KEY actually do at the database level?
  4. Q4Explain ON DELETE CASCADE in one sentence - and one risk.
  5. Q5Explain ON DELETE RESTRICT (or NO ACTION) in one sentence.
  6. Q6Explain ON DELETE SET NULL - when would you use it?
  7. Q7Difference between UNIQUE and PRIMARY KEY?
  8. Q8Can a UNIQUE column be NULL? Can a PRIMARY KEY column be NULL?
  9. Q9What's the difference between NOT NULL and a CHECK constraint?
  10. Q10What does DEFAULT do - and when is it applied?
  11. Q11Why is INSERT INTO ... VALUES (...) the basic form, and when is INSERT ... SELECT useful?
  12. Q12Explain UPDATE without WHERE - what's the danger?
  13. Q13Explain DELETE without WHERE - what's the danger?
  14. Q14Difference between DELETE and TRUNCATE in 2 sentences.
  15. Q15Why is TRUNCATE faster than DELETE?
  16. Q16Can you ROLLBACK a TRUNCATE?
  17. Q17Can you ROLLBACK a DELETE?
  18. Q18What is "referential integrity"?
  19. Q19What happens if you try to INSERT into a child table with a non-existent FK value?
  20. Q20Can you DROP a parent table if child tables reference it? What's the trick to do it safely?
  21. Q21Why are constraints checked AT WRITE TIME instead of at query time?
  22. Q22Difference between a CHECK constraint and a TRIGGER?
  23. Q23Why do experienced DBAs add NOT NULL to most columns?
  24. Q24Explain BEGIN/COMMIT/ROLLBACK in one sentence each.
  25. Q25After ROLLBACK, is the row "deleted" or "never inserted"? Explain.

ADD CONSTRAINTS TO YOUR TABLES (25) These run on the tables you created in Topic 3 inside practice.

  1. Q26Add a UNIQUE constraint on practice.customers.email.
  2. Q27Add a NOT NULL constraint on practice.customers.email.
  3. Q28Add a CHECK constraint to practice.products: price > 0.
  4. Q29Add a CHECK constraint to practice.product_reviews: rating BETWEEN 1 AND 5.
  5. Q30Add a FOREIGN KEY: practice.orders_demo.cust_id references practice.customers(cust_id).
  6. Q31Add ON DELETE CASCADE to the orders_demo -> customers FK. (Drop and recreate the FK.)
  7. Q32Add a NOT NULL constraint on practice.products.name.
  8. Q33Add a DEFAULT value FALSE to practice.products.in_stock.
  9. Q34Add a DEFAULT NOW() to practice.customers.created_at.
  10. Q35Add a multi-column UNIQUE constraint on practice.tier_master: (tier_name).
  11. Q36Add a CHECK on practice.employees: salary >= 0.
  12. Q37Add a FOREIGN KEY from practice.transactions.from_account -> bank_accounts.account_id.
  13. Q38Add a FOREIGN KEY from practice.transactions.to_account -> bank_accounts.account_id.
  14. Q39Add a CHECK on bank_accounts: balance >= 0.
  15. Q40Add a UNIQUE constraint on bank_accounts.holder + opened_on (composite).
  16. Q41Drop the CHECK constraint you added on products.price > 0. (You'll need to know its name - look it up first.)
  17. Q42Drop the UNIQUE constraint on customers.email.
  18. Q43Drop the FOREIGN KEY from orders_demo to customers.
  19. Q44Add a NOT NULL to feedback.rating.
  20. Q45Add a CHECK on feedback: rating BETWEEN 1 AND 5.
  21. Q46Add a DEFAULT 'pending' (TEXT) to a new status column in orders_demo. (Add the column first, then default.)
  22. Q47Add an inline CHECK constraint while creating a new table practice.scores (score_id SERIAL PK, score INT CHECK (score BETWEEN 0 AND 100)).
  23. Q48Create a new table practice.employees_with_fk that has a FOREIGN KEY to practice.tier_master(tier_id) on a column tier_id.
  24. Q49Add ON DELETE SET NULL to the FK in employees_with_fk -> tier_master.
  25. Q50Add a UNIQUE constraint on practice.transactions(from_account, txn_time) to prevent duplicate txns.

INSERT DATA

  1. Q51Insert one row into practice.customers with email '[email protected]', phone '+91 9000000001', is_active TRUE, created_at NOW().
  2. Q52Insert one row into practice.customers - let cust_id auto-generate (SERIAL), email '[email protected]'.
  3. Q53Insert 3 rows into practice.tier_master: (1, 'Bronze', 0), (2, 'Silver', 100), (3, 'Gold', 500).
  4. Q54Insert one row into practice.products: name 'Mango Juice', price 80.00, in_stock TRUE.
  5. Q55Insert one row into practice.products: name 'Premium Tea', price 350.00 - let in_stock take its DEFAULT.
  6. Q56Try inserting an order into orders_demo for a cust_id that doesn't exist (e.g., 99999). What error do you expect?
  7. Q57Insert a valid order into orders_demo for an existing customer.
  8. Q58Insert 5 employees into practice.employees with one INSERT statement (multi-row INSERT).
  9. Q59Insert one row into practice.bank_accounts: holder 'Rahul Sharma', account_type 'Savings', balance 50000, opened_on '2024-01-15'.
  10. Q60Insert 3 more bank_accounts in a single multi-row INSERT.
  11. Q61Insert a transaction: from_account 1, to_account 2, amount 5000, txn_time NOW().
  12. Q62Try inserting a transaction with from_account = 99999 (doesn't exist). What happens because of the FK?
  13. Q63Try inserting a customer with NULL email (after you added NOT NULL). What error do you expect?
  14. Q64Try inserting a product with price = -100. What error do you expect from the CHECK?
  15. Q65Insert one feedback row: customer_id 1, rating 5, freeform 'Loved it!'.
  16. Q66Try inserting a feedback with rating = 7. What error from the CHECK?
  17. Q67Insert into events_log: payload as JSON object {"event":"signup","city":"Mumbai"}, occurred_at NOW().
  18. Q68Insert into sessions: session_id gen_random_uuid(), user_id 1, started NOW(), ended NULL.
  19. Q69Insert a row into practice.brands: brand_name 'DesiSnacks', country 'India'.
  20. Q70Insert 3 cities into practice.cities in one statement: Mumbai/MH, Delhi/DL, Bengaluru/KA.
  21. Q71Insert 2 payment_modes: 'UPI'/TRUE, 'COD'/TRUE.
  22. Q72Insert one row into employees_with_fk: full_name 'Test User', tier_id 2 (must exist).
  23. Q73Try inserting a duplicate email into customers (after UNIQUE added). What error do you expect?
  24. Q74Insert via SELECT - populate practice.brands_copy (you'll need to create it first) from brands.
  25. Q75Insert a transaction that violates the UNIQUE (from_account, txn_time) constraint. What error?

UPDATE / DELETE / TRUNCATE / TRANSACTIONS

  1. Q76Update practice.customers SET is_active = FALSE WHERE cust_id = 1.
  2. Q77Update all products SET in_stock = TRUE.
  3. Q78Update practice.products SET price = price * 1.10 - give every product a 10% price hike.
  4. Q79Update practice.employees SET salary = 50000 WHERE salary IS NULL.
  5. Q80Update practice.customers SET phone = '+91 9999999999' WHERE email = '[email protected]'.
  6. Q81Update practice.bank_accounts SET balance = balance - 1000 WHERE account_id = 1.
  7. Q82Update practice.bank_accounts SET balance = balance + 1000 WHERE account_id = 2.
  8. Q83Delete one row: DELETE FROM customers WHERE cust_id = 1.
  9. Q84Delete all products where price < 50.
  10. Q85Delete all feedback with rating < 3.
  11. Q86Delete a customer WITH cascade - verify their orders_demo rows are also deleted (because of ON DELETE CASCADE you added earlier).
  12. Q87TRUNCATE practice.feedback - fast wipe.
  13. Q88TRUNCATE practice.events_log RESTART IDENTITY - wipe AND reset the BIGSERIAL counter.
  14. Q89Run a transaction: BEGIN; UPDATE bank_accounts SET balance = balance - 5000 WHERE account_id = 1; UPDATE bank_accounts SET balance = balance + 5000 WHERE account_id = 2; COMMIT.
  15. Q90Run a transaction that you ROLLBACK to verify rollback works: BEGIN; UPDATE bank_accounts SET balance = 0; ROLLBACK. Then SELECT to verify balances unchanged.
  16. Q91Update product names: SET name = INITCAP(name) on practice.products.
  17. Q92Update practice.employees SET dept_name = 'Engineering' WHERE dept_name IS NULL.
  18. Q93Delete from practice.notifications where sent_at < CURRENT_DATE - INTERVAL '30 days' (purge old notifications).
  19. Q94Update practice.subscriptions SET auto_renew = FALSE WHERE ends < CURRENT_DATE.
  20. Q95Delete from practice.login_log where login_time < CURRENT_DATE - INTERVAL '90 days'.
  21. Q96TRUNCATE practice.notifications.
  22. Q97Delete every row from sessions where ended IS NOT NULL (already closed).
  23. Q98Update orders_demo SET total = 0 WHERE total IS NULL.
  24. Q99Inside a transaction, INSERT a customer, then ROLLBACK. After ROLLBACK, query - should the customer exist?
  25. Q100Wipe ALL practice tables by using TRUNCATE ... CASCADE on practice.customers (cascades to children).

Combined ideas, multi-step thinking

CONSTRAINTS & DML DEEPER CONCEPTUAL

  1. Q1Compare ON DELETE CASCADE vs ON DELETE SET NULL vs ON DELETE RESTRICT - give a real-world example where each is correct.
  2. Q2What does "DEFERRABLE INITIALLY DEFERRED" mean on a FK - when do you need it?
  3. Q3Difference between a UNIQUE constraint and a UNIQUE INDEX - which is more flexible?
  4. Q4What is a PARTIAL UNIQUE index - give a use case from a multi-tenant SaaS.
  5. Q5Why can't a CHECK constraint reference ANOTHER table? What do you use instead?
  6. Q6Explain how PostgreSQL validates CHECK constraints when you ALTER TABLE ADD CHECK on existing data.
  7. Q7What is INSERT ... ON CONFLICT (upsert) - and what TWO patterns does it support?
  8. Q8What does the RETURNING clause do - give a use case where it saves a round trip.
  9. Q9Compare DELETE vs TRUNCATE vs DROP TABLE - when is each appropriate?
  10. Q10Why does PostgreSQL allow you to UPDATE multiple tables in one transaction but NOT in one UPDATE statement?
  11. Q11Explain the "lost update" problem and how SELECT FOR UPDATE solves it.
  12. Q12Compare optimistic vs pessimistic locking - which is which and when do you use each?
  13. Q13What is a SAVEPOINT - and when is it more useful than just COMMIT/ROLLBACK?
  14. Q14Why is INSERT ... SELECT often used in ETL pipelines?
  15. Q15Compare UPDATE ... SET col = (SELECT ...) (correlated) vs UPDATE ... FROM (joined). Which is generally faster?
  16. Q16Explain what "tuple visibility" means in PostgreSQL - and why DELETE doesn't actually free disk space.
  17. Q17Why is VACUUM needed in PostgreSQL - what does it actually do?
  18. Q18What is autovacuum - and when does it run?
  19. Q19Compare INSERT INTO ... VALUES (...) vs prepared statements - when does each win?
  20. Q20What happens if you INSERT 1000 rows in a single INSERT vs 1000 separate INSERT statements (no transaction)?
  21. Q21Why do experienced engineers wrap bulk DML in transactions even for INSERTs?
  22. Q22Explain what "phantom reads" are and which isolation level prevents them.
  23. Q23Compare READ COMMITTED vs SERIALIZABLE isolation levels - which is the PostgreSQL default?
  24. Q24What is a TRIGGER - give one example of when it's the right tool vs when it's an anti-pattern.
  25. Q25Why do many teams BAN triggers in production code - even though PostgreSQL supports them?

ADVANCED CONSTRAINT SCENARIOS

  1. Q26Add a multi-column UNIQUE on practice.transactions: (from_account, to_account, txn_time) - prevent exact-duplicate transfers at the same moment.
  2. Q27Add a CHECK on practice.bank_accounts that balance must be either 0 OR positive when account_type is 'Savings'.
  3. Q28Add a DEFERRABLE FK between two tables so you can insert children before parents in a single transaction.
  4. Q29Add a PARTIAL UNIQUE INDEX to practice.addresses ensuring only ONE is_default = TRUE per customer.
  5. Q30Add a CHECK to practice.subscriptions ensuring ends > starts.
  6. Q31Add a CHECK to practice.products ensuring discount_percent BETWEEN 0 AND 100.
  7. Q32Add a CHECK on practice.employees ensuring joining_date <= CURRENT_DATE (no future-dated joins).
  8. Q33Add a CHECK on practice.product_reviews ensuring rating BETWEEN 1 AND 5 AND comment IS NOT NULL when rating <= 2 (force feedback on low ratings).
  9. Q34Add a CHECK on practice.feedback ensuring (rating = 5 AND freeform IS NOT NULL) OR rating < 5 (i.e., 5-star reviews must have a comment).
  10. Q35Add a composite CHECK on practice.coupons (if you created it from Topic 3): valid_until > CURRENT_DATE OR uses_count >= max_uses.
  11. Q36Add an ON DELETE SET NULL between practice.employees_with_fk(tier_id) and practice.tier_master(tier_id).
  12. Q37Add an ON DELETE CASCADE between practice.transactions(from_account) and practice.bank_accounts(account_id) - what's the risk?
  13. Q38Drop a CHECK constraint by looking it up in pg_constraint first.
  14. Q39Add a CHECK using a complex expression: ON practice.products, expensive products (price > 10000) must have a sku_code.
  15. Q40Add an EXCLUSION constraint on practice.subscriptions preventing two overlapping date ranges for the same user_id (advanced - needs btree_gist).
  16. Q41Add a NOT NULL on practice.customers.created_at with a DEFAULT NOW() in one statement.
  17. Q42Drop the NOT NULL on practice.customers.is_active (allow NULL again).
  18. Q43Set the DEFAULT of practice.products.in_stock to FALSE - without affecting existing rows.
  19. Q44Use NOT VALID to add a constraint that only checks new rows, then VALIDATE later.
  20. Q45Add a UNIQUE constraint on (LOWER(email)) using a unique index expression.
  21. Q46Create a constraint that ensures email contains '@' - use a CHECK with the regex operator.
  22. Q47Create a constraint that ensures phone is exactly 10 digits if not NULL (use CHECK with LENGTH).
  23. Q48Add a CHECK ensuring pincode is a 6-digit number stored as VARCHAR.
  24. Q49Add a CHECK ensuring discount_percent < tax_rate doesn't lead to negative net (cross-column CHECK).
  25. Q50List all constraints on practice.products using pg_constraint.

BULK DML PATTERNS

  1. Q51INSERT 1000 rows into practice.notifications using INSERT ... SELECT from generate_series(1, 1000).
  2. Q52INSERT INTO practice.archive_orders SELECT * FROM practice.orders_demo WHERE order_date < '2024-01-01'.
  3. Q53UPDATE all products in practice.products that have NULL discount_percent -> SET to 0.
  4. Q54UPDATE practice.products SET price = price * 1.10 WHERE brand_id IN (1, 2, 3).
  5. Q55UPDATE FROM pattern: update practice.customers SET tier = m.tier_id FROM practice.tier_master m WHERE m.tier_id = practice.customers.tier_id.
  6. Q56DELETE FROM practice.notifications USING practice.customers WHERE notifications.user_id = customers.cust_id AND customers.is_active = FALSE.
  7. Q57INSERT INTO practice.products (name, price, in_stock) VALUES (...), (...), (...) - 10 rows in one statement.
  8. Q58Upsert: INSERT INTO practice.tier_master VALUES (4, 'Diamond', 1000) ON CONFLICT (tier_id) DO UPDATE SET tier_name = EXCLUDED.tier_name.
  9. Q59Upsert with DO NOTHING: INSERT INTO practice.customers (email) VALUES ('[email protected]') ON CONFLICT (email) DO NOTHING.
  10. Q60Use RETURNING to get the newly inserted customer's auto-generated cust_id.
  11. Q61INSERT INTO practice.orders_demo (cust_id, total, order_date) VALUES (1, 5000, CURRENT_DATE) RETURNING order_id.
  12. Q62UPDATE ... RETURNING: update a customer's phone and return the OLD AND NEW values.
  13. Q63DELETE ... RETURNING: delete an inactive customer and return the deleted row for an audit log.
  14. Q64Use INSERT ... SELECT to copy 100 rows from practice.products to practice.products_v2.
  15. Q65UPDATE with CASE: SET practice.products.status = CASE WHEN price > 1000 THEN 'premium' ELSE 'standard' END.
  16. Q66UPDATE ... SET multiple columns at once: SET price = price * 1.05, updated_at = NOW().
  17. Q67DELETE rows older than 90 days from practice.notifications.
  18. Q68DELETE in batches: DELETE FROM ... WHERE id IN (SELECT id FROM ... LIMIT 1000) - repeat.
  19. Q69INSERT ... SELECT with WHERE: only copy rows that meet a condition.
  20. Q70INSERT into a partitioned table - PostgreSQL routes to the right partition automatically.
  21. Q71TRUNCATE multiple tables in one statement: TRUNCATE practice.notifications, practice.events_log.
  22. Q72TRUNCATE with RESTART IDENTITY to reset the SERIAL counter.
  23. Q73UPDATE with subquery in SET: update each customer's total_spend = (SELECT SUM ... ).
  24. Q74Use UPDATE FROM with multi-table join.
  25. Q75DELETE with USING + multi-table join.

TRANSACTIONAL CRUD PATTERNS

  1. Q76Wrap an UPDATE in a transaction; SELECT to verify; ROLLBACK if wrong.
  2. Q77Wrap a DELETE in a transaction; SELECT count(*) before COMMIT to verify scope.
  3. Q78Multi-step transaction: INSERT into orders, INSERT into order_items, COMMIT. If items fail, ROLLBACK leaves orders untouched.
  4. Q79Use SAVEPOINT inside a transaction: complete step 1, SAVEPOINT s1, attempt step 2, ROLLBACK TO s1 on error.
  5. Q80Demonstrate how SELECT FOR UPDATE prevents two sessions from updating the same row simultaneously.
  6. Q81Use a transaction to safely transfer money between two bank_accounts (debit + credit + log = all-or-nothing).
  7. Q82Wrap a multi-INSERT in a transaction so a single failure rolls everything back.
  8. Q83Use BEGIN ... ROLLBACK to TEST an UPDATE without committing (sanity check).
  9. Q84Verify with SELECT before and after an UPDATE inside a transaction.
  10. Q85Inside a transaction, use SELECT count(*) BEFORE and AFTER a DELETE for verification.
  11. Q86Demonstrate a transaction that adds a customer, fails on FK violation, ROLLBACKs cleanly.
  12. Q87Use SET LOCAL inside a transaction (e.g., SET LOCAL statement_timeout = '5s') - what does LOCAL mean?
  13. Q88Use a deferred FK to insert child rows before parent in one transaction (Q28 setup required).
  14. Q89Use SELECT FOR UPDATE NOWAIT to fail immediately if the row is locked (don't wait).
  15. Q90Use SELECT FOR UPDATE SKIP LOCKED - useful for queue-style workers.
  16. Q91Demonstrate isolation: in session A, BEGIN + UPDATE without commit; in session B, SELECT - what does B see?
  17. Q92Use a transaction to rename a column safely (multi-step: rename -> verify -> COMMIT, OR ROLLBACK).
  18. Q93Run a transaction that creates a table, inserts data, then ROLLBACK - verify the table never persists.
  19. Q94Show that DDL inside a transaction is rolled back in PostgreSQL (unlike some other databases).
  20. Q95Use SAVEPOINT in a long ETL transaction so a single bad row doesn't kill the whole load.
  21. Q96Combine INSERT ... ON CONFLICT inside a transaction.
  22. Q97Use a transaction with two UPDATEs that depend on each other; verify net effect with SELECT after.
  23. Q98Inside a transaction, DELETE rows + INSERT replacements - atomic data refresh.
  24. Q99Show how a transaction that's been open for 1 hour can cause bloat (long-running transactions block VACUUM).
  25. Q100Use BEGIN READ ONLY to start a read-only transaction (helpful for analyst safety).

Interview grade, edge cases

ADVANCED DML - CONCEPTUAL

  1. Q1What is "UPSERT" - how does INSERT ... ON CONFLICT implement it?
  2. Q2Compare ON CONFLICT DO NOTHING vs DO UPDATE - when each?
  3. Q3Explain EXCLUDED pseudo-table in ON CONFLICT DO UPDATE.
  4. Q4What is MERGE (PG15+) - and what does INSERT/UPDATE/DELETE in one statement enable?
  5. Q5Compare MERGE vs INSERT ON CONFLICT - when does MERGE win?
  6. Q6What is a writeable CTE - and what's a real RetailMart use case?
  7. Q7Why does INSERT ... RETURNING + WITH let you chain a write into another query atomically?
  8. Q8Explain the visibility rules of CTE-DML - when does the SECOND CTE see the FIRST's changes?
  9. Q9Compare COPY vs INSERT for bulk loading - speed, transactionality.
  10. Q10What is "WITH (NULL '')" in COPY - and other handy COPY options?
  11. Q11How does SELECT FOR UPDATE differ from SELECT FOR UPDATE SKIP LOCKED?
  12. Q12Explain how UPDATE locks work - and why ORDER BY id can prevent deadlocks.
  13. Q13What is a "queue table pattern" - how does SKIP LOCKED build a parallel-safe queue?
  14. Q14Walk through how INSERT inside a BEFORE INSERT trigger can recurse infinitely.
  15. Q15Compare BEFORE INSERT trigger that returns NEW vs that returns NULL.
  16. Q16What is INSTEAD OF trigger - and what's the only relation type it applies to?
  17. Q17Explain when AFTER triggers run vs BEFORE triggers.
  18. Q18Why is STATEMENT-level trigger different from ROW-level - when do you use each?
  19. Q19What is a transition table (REFERENCING NEW TABLE AS) in a trigger?
  20. Q20How does ON CONFLICT (key1, key2) WHERE pred - partial UPSERT - work?
  21. Q21Compare TRUNCATE vs DELETE WHERE 1=1 - performance, transactional behavior.
  22. Q22What does the RESTART IDENTITY clause of TRUNCATE do?
  23. Q23Explain why DELETE inside a writeable CTE can return rows for further processing.
  24. Q24What is a "soft delete" pattern - set deleted_at instead of DELETE - pros/cons.
  25. Q25Walk through transactional boundaries: when does a DML inside a function actually commit?

UPSERT, MERGE, ADVANCED CONFLICTS

  1. Q26INSERT ON CONFLICT on customer email - DO UPDATE SET full_name = EXCLUDED.full_name.
  2. Q27INSERT ON CONFLICT (email) DO NOTHING.
  3. Q28INSERT ON CONFLICT WHERE deleted_at IS NULL - partial-unique conflict target.
  4. Q29Build inventory_snapshot upsert by (warehouse_id, product_id, snapshot_date).
  5. Q30UPSERT loyalty.members points balance - add new points to existing balance.
  6. Q31UPSERT ad_campaign daily spend - sum into running total.
  7. Q32UPSERT page_view session - increment view count.
  8. Q33UPSERT ticket - set last_updated to now() on conflict.
  9. Q34UPSERT supplier - only update if supplier_status changed (WHERE EXCLUDED.status <> existing.status).
  10. Q35Build a "counter table" upsert that increments a counter atomically.
  11. Q36MERGE INTO customers USING staging_customers - INSERT new, UPDATE changed, DELETE removed.
  12. Q37MERGE INTO products USING price_updates - only WHEN MATCHED THEN UPDATE price.
  13. Q38MERGE INTO inventory USING delta - UPDATE quantity = quantity + delta.
  14. Q39MERGE INTO loyalty.members WHEN NOT MATCHED INSERT ELSE UPDATE.
  15. Q40MERGE INTO sales.orders USING refund_set - UPDATE order_status = 'Refunded' WHEN MATCHED.
  16. Q41RETURNING * from INSERT - capture the auto-generated id.
  17. Q42RETURNING cust_id, order_id from a multi-row INSERT.
  18. Q43RETURNING old.* from UPDATE - show old values vs new.
  19. Q44WITH inserted AS (INSERT ... RETURNING *) SELECT * FROM inserted.
  20. Q45WITH deleted AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM deleted.
  21. Q46UPSERT batch: 100 customers from a staging table in a single INSERT ... ON CONFLICT statement.
  22. Q47UPSERT with COALESCE: only overwrite NULL fields, keep existing non-NULL.
  23. Q48Conditional UPSERT: UPDATE only if EXCLUDED.last_seen > existing.last_seen (idempotency).
  24. Q49INSERT ... ON CONFLICT (email) DO UPDATE SET tier_id = GREATEST(EXCLUDED.tier_id, existing.tier_id).
  25. Q50MERGE with a DO NOTHING source - show that MERGE can be a no-op.

WRITEABLE CTE + RETURNING

  1. Q51WITH archived AS (DELETE FROM old_orders RETURNING *) INSERT INTO orders_archive SELECT * FROM archived.
  2. Q52WITH new_c AS (INSERT INTO customers ... RETURNING customer_id) INSERT INTO loyalty.members SELECT customer_id, 1, 0 FROM new_c.
  3. Q53WITH up AS (UPDATE inventory SET qty = qty - 1 WHERE ... RETURNING product_id) INSERT INTO audit_log SELECT product_id, now() FROM up.
  4. Q54Two-step write: WITH ins AS (INSERT ...) UPDATE other SET counter = counter + 1 WHERE id IN (SELECT ... FROM ins).
  5. Q55CTE-DELETE with RETURNING that joins back to log table.
  6. Q56WITH split AS (INSERT INTO a ... RETURNING *), split2 AS (INSERT INTO b ...) SELECT 1; - multi-target write.
  7. Q57Insert order + N order_items in single statement using WITH ... RETURNING.
  8. Q58Move "old" rows from active to history in one CTE-chained transaction.
  9. Q59UPDATE customers + RETURNING old_tier, new_tier - log changes to audit.
  10. Q60DELETE soft-deleted customers (where deleted_at < now() - 90 days) RETURNING to backup.
  11. Q61WITH stats AS (UPDATE products SET stock = stock - 5 RETURNING product_id, stock) SELECT * FROM stats WHERE stock < 10.
  12. Q62INSERT ... RETURNING then LATERAL join to compute derived values per inserted row.
  13. Q63UPSERT + RETURNING to detect whether row was INSERTed or UPDATEd (xmax = 0 trick).
  14. Q64Bulk DELETE with batching: DELETE LIMIT 1000 in a loop until no more rows.
  15. Q65Atomic counter increment with RETURNING new_value.
  16. Q66WITH old AS (SELECT FOR UPDATE ...) UPDATE ... - pattern for serialized writes.
  17. Q67WITH new AS (INSERT ... RETURNING id) DELETE FROM staging WHERE id IN (SELECT id FROM new).
  18. Q68Insert into 3 tables (transactionally) using one statement with CTEs.
  19. Q69Move records from staging to live with deduplication: WITH dedup AS (SELECT DISTINCT ON ...) INSERT INTO live SELECT * FROM dedup.
  20. Q70UPDATE with self-reference via CTE: WITH ranks AS (SELECT ...) UPDATE t SET rank = ranks.rank FROM ranks WHERE t.id = ranks.id.
  21. Q71Insert + LOG audit row with NOW() - atomic.
  22. Q72Implement "move row to archive" with WITH d AS (DELETE ... RETURNING *) INSERT INTO archive.
  23. Q73Implement "shift IDs" - DELETE+INSERT under one transaction (controversial pattern).
  24. Q74Implement "batch reassign" - UPDATE 100 customers' tier in single statement.
  25. Q75Implement "rebuild" pattern: TRUNCATE child; INSERT child FROM parent; - all in one transaction.

BULK LOAD, CONCURRENCY, TRIGGERS

  1. Q76COPY products_staging FROM '/tmp/products.csv' WITH (FORMAT csv, HEADER true).
  2. Q77COPY with FREEZE - when can you use it (only on empty table in same transaction).
  3. Q78INSERT INTO ... SELECT FROM ... LIMIT 10000 - batched migration.
  4. Q79SELECT ... FOR UPDATE SKIP LOCKED LIMIT 100 - worker grabbing jobs.
  5. Q80SELECT ... FOR NO KEY UPDATE - when this weaker lock is correct.
  6. Q81UPDATE ... ORDER BY id - avoid deadlocks across concurrent UPDATEs.
  7. Q82Detect deadlock: two transactions update rows in opposite order.
  8. Q83Build a job queue: INSERT job -> worker SELECT FOR UPDATE SKIP LOCKED -> UPDATE status='done'.
  9. Q84Build a BEFORE INSERT trigger that auto-fills created_at.
  10. Q85Build an AFTER INSERT trigger that logs to an audit table.
  11. Q86Build a BEFORE UPDATE trigger that prevents changing immutable columns.
  12. Q87Build an AFTER DELETE trigger that copies the row to deleted_archive.
  13. Q88Build an INSTEAD OF INSERT trigger on a view that distributes to underlying tables.
  14. Q89Build a STATEMENT-level trigger that fires once per INSERT, regardless of row count.
  15. Q90Use REFERENCING NEW TABLE AS to inspect all inserted rows in a statement trigger.
  16. Q91Build a trigger that prevents UPDATE of more than 1 row per statement (safety guard).
  17. Q92Build a trigger that auto-updates updated_at on every UPDATE.
  18. Q93Build a trigger that recomputes a denormalized total in a parent table.
  19. Q94Build a constraint trigger (DEFERRABLE) that checks at COMMIT.
  20. Q95Build a trigger that maintains a materialized count column.
  21. Q96Bulk load with concurrent inserts: how to safely COPY into a hot table.
  22. Q97UNLOGGED TABLE for staging - show speed difference vs LOGGED.
  23. Q98INSERT 1 million rows efficiently: COPY > INSERT-many-values > 1M single-row INSERTs.
  24. Q99INSERT INTO staging SELECT generate_series - useful for test data.
  25. Q100Build a complete "ETL stage -> upsert" pattern: COPY staging -> MERGE into target -> DROP staging.

Production scenarios, optimisation

IDEMPOTENT WRITE PATTERNS

  1. Q1Idempotent INSERT with idempotency key.
  2. Q2Idempotent UPSERT using natural key.
  3. Q3Idempotent DELETE - safe to retry.
  4. Q4ON CONFLICT DO NOTHING vs DO UPDATE - when each is idempotent.
  5. Q5WITH inserted AS (...) idempotent insert + log.
  6. Q6Idempotent token-based "have I processed this event?" check.
  7. Q7Idempotent counter increment using GREATEST.
  8. Q8Idempotent state transition with WHERE status = expected_previous.
  9. Q9Idempotent loyalty points credit (don't double-credit).
  10. Q10Idempotent payment recording.
  11. Q11Idempotent inventory deduction.
  12. Q12Idempotent email_sent tracking.
  13. Q13Idempotent webhook receipt.
  14. Q14Idempotent shipment tracking update.
  15. Q15Idempotent campaign attribution.
  16. Q16Idempotent customer signup (allow retry, same row).
  17. Q17Idempotent order placement.
  18. Q18Idempotent refund.
  19. Q19Idempotent review submission.
  20. Q20Idempotent ticket creation.
  21. Q21Idempotent tier upgrade.
  22. Q22Idempotent ad spend recording.
  23. Q23Idempotent push notification log.
  24. Q24Idempotent fraud flag set.
  25. Q25Build a generic "idempotency key" table + middleware function.

CDC, AUDIT, EVENT SOURCING

  1. Q26Build an audit log of all INSERTs on sales.orders.
  2. Q27Build an audit log of all UPDATEs on customers.customers.
  3. Q28Build an audit log of all DELETEs (with full row).
  4. Q29Implement event-sourced order_events table.
  5. Q30Replay events to reconstruct current state.
  6. Q31Capture change with row_id + jsonb_diff.
  7. Q32CDC via logical replication slot - read change stream.
  8. Q33CDC via Debezium -> Kafka - pattern overview.
  9. Q34Use REFERENCING NEW TABLE AS for bulk audit.
  10. Q35Avoid trigger overhead with batched audit (one row per N).
  11. Q36Trigger-based "snapshot before update".
  12. Q37Versioned rows (record-history table).
  13. Q38Soft-delete with audit.
  14. Q39Use "outbox" pattern for reliable event emission.
  15. Q40Use logical replication to feed events to consumers.
  16. Q41Stream of state changes into JSONB events.
  17. Q42Event store with snapshot every N events.
  18. Q43Detect "lost" events via gap detection in sequence numbers.
  19. Q44Build a "replay log" for testing.
  20. Q45CDC + sequence number monotonicity check.
  21. Q46Event timestamping (logical vs wall-clock).
  22. Q47Build an outbox poller with FOR UPDATE SKIP LOCKED.
  23. Q48Implement at-least-once delivery + dedupe at consumer.
  24. Q49Detect schema drift via change events.
  25. Q50Capture row-level statistics into a time-series audit.

CONCURRENT WRITES

  1. Q51Two writers UPDATE same row - last-write-wins demo.
  2. Q52Two writers + SELECT FOR UPDATE - serialize.
  3. Q53Two writers + SERIALIZABLE - abort one.
  4. Q54Deadlock between two UPDATEs in opposite order.
  5. Q55Resolve deadlock by ORDER BY id.
  6. Q56Optimistic locking via version column.
  7. Q57Pessimistic locking via row lock.
  8. Q58Compare-and-set update pattern.
  9. Q59Atomic counter increment 100 sessions in parallel.
  10. Q60Bank-style transfer (debit + credit) - must be atomic.
  11. Q61Order checkout = INSERT order + UPDATE inventory + DEDUCT loyalty.
  12. Q62Queue table: 10 workers + SKIP LOCKED.
  13. Q63Lease pattern: claim job for N seconds.
  14. Q64Leader election via advisory lock.
  15. Q65Distributed counter via N sub-counters + SUM on read.
  16. Q66Idempotent + concurrent: 2 retries of same checkout - only one effects.
  17. Q67Inventory hot row: shard into 10 buckets.
  18. Q68Outbox poller: 5 parallel workers.
  19. Q69CDC subscriber: pace yourself.
  20. Q70Update with retry-on-serialization-failure.
  21. Q71ETL backfill: don't overload primary.
  22. Q72Read-replica routing for offline reads.
  23. Q73Write-ahead queue (Postgres NOTIFY/LISTEN).
  24. Q74Implement "throttle ALTER" - cap concurrent DDL.
  25. Q75Implement "cooperative throttle" via advisory lock counter.

BULK LOAD & STREAMING

  1. Q76COPY 1M rows from CSV.
  2. Q77COPY with FREEZE.
  3. Q78COPY via stdin with on-the-fly transformation.
  4. Q79Streaming INSERT from another DB via FDW.
  5. Q80Bulk UPSERT 100k rows.
  6. Q81Bulk DELETE with batching (LIMIT 10000 loop).
  7. Q82Bulk MERGE source -> target.
  8. Q83UNLOGGED staging + COPY + INSERT INTO main.
  9. Q84Parallel COPY workers (multiple files).
  10. Q85Use pg_bulkload extension for faster ingest.
  11. Q86Streaming ingestion via INSERT ... RETURNING + Kafka.
  12. Q87Backpressure: pause ETL when WAL writer falls behind.
  13. Q88ETL idempotency: re-run safe via natural key UPSERT.
  14. Q89ETL checkpoint: track last_processed_id per source.
  15. Q90ETL dedup: WITH dedup AS (DISTINCT ON ...) INSERT INTO ...
  16. Q91ETL with FK violations: skip + log.
  17. Q92ETL with NOT NULL violations: substitute default.
  18. Q93ETL with TYPE-cast failures: route to dead-letter table.
  19. Q94ETL with DUPLICATE key: ON CONFLICT DO NOTHING.
  20. Q95ETL with retries via WITH ... ON CONFLICT.
  21. Q96Bulk load test data using generate_series.
  22. Q97Migration from MySQL via FDW (mysql_fdw).
  23. Q98Migration to ClickHouse: pg_dump COPY + clickhouse-client.
  24. Q99Migration to Snowflake: pg_dump -> S3 -> Snowflake.
  25. Q100Build a complete ETL: source -> staging -> merge -> archive.