How to fix In failed SQL transaction in PostgreSQL

The primary way to recover from a failed transaction is to issue a ROLLBACK command. This will discard all changes made in the transaction and reset the transaction state so new queries can be executed.

ROLLBACK;

After executing ROLLBACK, you can either commit what you've done before the error or restart your transaction block.

Examine your application logs or the PostgreSQL server logs to find which SQL statement first caused the error. This will help you understand what constraint was violated or what syntax issue occurred.

-- Check PostgreSQL logs (path varies by installation)
SELECT * FROM pg_read_file('log/postgresql.log') LIMIT 100;

Common causes include attempting to insert a duplicate primary key, violating a unique constraint, or using invalid SQL syntax.

If you want to recover from an error without rolling back the entire transaction, use a SAVEPOINT. This creates a nested transaction checkpoint that you can roll back to independently.

BEGIN;
  INSERT INTO users (id, email) VALUES (1, '[email protected]');
  SAVEPOINT sp1;
  -- This might fail due to a constraint:
  INSERT INTO users (id, email) VALUES (1, '[email protected]');
ROLLBACK TO SAVEPOINT sp1;  -- Rolls back only the failed insert
  -- Transaction continues; the first insert is still there
COMMIT;  -- Commits the first insert only

After rolling back to a savepoint, you can either release it or continue with other operations.

Once you've identified why the transaction failed, fix the underlying issue:
- Verify unique constraint values before inserting
- Check that foreign key references exist
- Ensure the user has proper permissions
- Verify all required columns have values
- Use proper SQL syntax

Then retry the operation:

BEGIN;
  -- Fix applied: use a unique email or different primary key
  INSERT INTO users (id, email) VALUES (2, '[email protected]');
COMMIT;

The best solution is to handle errors gracefully in your application code rather than letting transactions remain open. Most database drivers support implicit savepoints or transaction error handlers.

For Node.js (node-postgres):

try {
  await client.query('BEGIN');
  await client.query('INSERT INTO users ...');
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  console.error('Transaction failed:', err.message);
}

For Python (psycopg2):

try:
    cursor.execute('BEGIN')
    cursor.execute('INSERT INTO users ...')
    cursor.execute('COMMIT')
except psycopg2.Error as e:
    cursor.execute('ROLLBACK')
    print(f'Transaction failed: {e}')

Always rollback on error and restart the transaction from scratch rather than trying to continue in a failed state.