Master PostgreSQL object-relational database design, SQL queries, indexes, JSONB document storage, CTEs, and ACID transactions.
Before starting: Basic SQL and a local PostgreSQL instance.
Practice project: Test a transaction using a temporary table.
Foundation and first practice
- Practice a PostgreSQL transaction and rollback
A transaction groups statements into a unit of work. ROLLBACK discards the changes made by the current transaction. Use a temporary table to practice without changing permanent tables. PostgreSQL differs from MySQL and SQLite, so check its own syntax and transaction behavior instead of treating every SQL example as interchangeable.
PostgreSQL; same session for all statements
CREATE TEMP TABLE practice_scores (score integer); BEGIN; INSERT INTO practice_scores VALUES (75); SELECT COUNT(*) FROM practice_scores; ROLLBACK; SELECT COUNT(*) FROM practice_scores;Check the output
The first count is 1. After ROLLBACK the count is 0. The temporary table remains until the session ends.Repeat with COMMIT. Explain why changes made before BEGIN are not undone by this ROLLBACK.
Open the PostgreSQL learning path on DevStudyHub · Play coding games on this blog
No comments:
Post a Comment
Please do not enter any spam link in the comment box.