arvb.net

SQL Transactions and Errors

3 min read

Basic INSERT Syntax Recap

Using Transactions

Transactions group multiple SQL statements into a single unit that either commits as a whole or rolls back entirely on error. This ensures data integrity.

Example: Simple Transaction

BEGIN;  -- Start the transaction
 
INSERT INTO orders (user_id, amount, status)
VALUES (1, 100.50, 'pending');
 
COMMIT;  -- Commit the transaction if everything is OK

If an error occurs between BEGIN and COMMIT, you can use ROLLBACK to undo all changes:

BEGIN;
 
-- Attempt an insert
INSERT INTO orders (user_id, amount, status)
VALUES (1, 100.50, 'pending');
 
-- If something goes wrong, rollback:
ROLLBACK;

Example: Transaction with Savepoint for Partial Rollback

Using savepoints allows you to roll back part of a transaction while keeping previous successful operations intact.

BEGIN;
 
-- First, perform a valid insert
INSERT INTO orders (user_id, amount, status)
VALUES (1, 100.50, 'pending');
 
-- Create a savepoint before the next operation
SAVEPOINT sp1;
 
-- Attempt an insert that might fail (e.g., negative amount violating a CHECK constraint)
INSERT INTO orders (user_id, amount, status)
VALUES (1, -50.00, 'pending');
 
-- If the above insert fails, rollback to the savepoint
ROLLBACK TO SAVEPOINT sp1;
 
-- Continue with a valid insert
INSERT INTO orders (user_id, amount, status)
VALUES (1, 150.75, 'shipped');
 
COMMIT;

Error Handling with ON CONFLICT

For handling duplicate entries or constraint violations during an insert, PostgreSQL offers the ON CONFLICT clause, often referred to as an “upsert.”

INSERT INTO users (user_id, username, email)
VALUES (5, 'new_user', 'new@example.com')
ON CONFLICT (user_id) 
DO NOTHING;

Or to update the record if a conflict occurs:

INSERT INTO users (user_id, username, email)
VALUES (5, 'new_user', 'new@example.com')
ON CONFLICT (user_id) 
DO UPDATE SET email = EXCLUDED.email;

Note: EXCLUDED.email refers to the value that was attempted to be inserted.

Error Handling with PL/pgSQL Exception Blocks

In PostgreSQL, you can handle errors using an anonymous code block with an EXCEPTION clause. This is useful when writing functions or scripts that require procedural error handling.

Example: Using a DO Block

DO $$
BEGIN
   INSERT INTO users (user_id, username, email)
   VALUES (5, 'new_user', 'new@example.com');
EXCEPTION WHEN unique_violation THEN
   RAISE NOTICE 'User with user_id 5 already exists. Skipping insert.';
END $$;
  • Explanation:
    • The block attempts to insert a new user.
    • If a unique_violation error occurs (e.g., duplicate user_id), it catches the exception and logs a notice without aborting the entire operation.

Final Notes

  • Transactions are essential for maintaining data integrity, especially in multi-step operations.
  • Savepoints allow partial rollbacks within a transaction.
  • ON CONFLICT provides a simple way to handle duplicate key errors during an insert.
  • PL/pgSQL Exception Blocks offer a procedural approach to handle errors and maintain control flow.

Graph View

Backlinks