PostgreSQL – COMMIT

The COMMIT command in PostgreSQL is pivotal for saving changes made during a transaction. Without issuing a COMMIT, the changes will not be reflected in the database, and any data manipulation performed will be lost once the session ends. To save the changes done in a transaction, we should COMMIT that transaction for sure.

Let us get a better understanding of the COMMIT command in PostgreSQL with examples to help you understand its importance and functionality.

Syntax

COMMIT TRANSACTION;
-- or
COMMIT;
-- or
END TRANSACTION;

All three variations achieve the same goal: they save the changes made during the current transaction to the database. Unlike other database languages in PostgreSQL, we commit the transaction in 3 different forms which are mentioned above.

PostgreSQL COMMIT Command Example

Now for getting good understanding of the use of COMMIT command we will first create a table for examples.

CREATE TABLE BankStatements (
    customer_id serial PRIMARY KEY,
    full_name VARCHAR NOT NULL,
    balance INT
);
INSERT INTO BankStatements (
    customer_id ,
    full_name,
    balance
)
VALUES
    (1, 'Sekhar rao', 1000),
    (2, 'Abishek Yadav', 500),
    (3, 'Srinivas Goud', 1000);

Now as  the table is ready we will understand about commit

Example 1: Inserting Data with COMMIT

We will insert a new record into the ‘BankStatements' table and commit the transaction.

BEGIN;

 INSERT INTO BankStatements (
     customer_id,
    full_name,
        balance
)
    VALUES
             ( 4, 'Priya chetri', 500 );
        
COMMIT;

Output:

Explanation: The new record is saved to the database, and the changes are now permanent.

Example 2: Updating Data and Understanding Transaction Behavior

In this example, we will update the balance of customers without initially committing the transaction, observe the data, and then commit the changes.

BEGIN;


    UPDATE BankStatements
      SET balance = balance - 500
      WHERE 
      customer_id = 1;
      
      // displaying data before
      // committing the transaction
      SELECT customer_id, full_name, balance
      FROM BankStatements;
      
      UPDATE BankStatements
      SET balance = balance + 500
      WHERE 
      customer_id = 2;
       
COMMIT;

// displaying data after
// committing the transaction
SELECT customer_id, full_name, balance
FROM BankStatements;

Output:

Output Before COMMIT: The changes will not be visible to other sessions or transactions until the COMMIT command is executed.

Output After COMMIT: The updated balances will be reflected and visible to all subsequent sessions.

Important Points About COMMIT Command in PostgreSQL

• The PostgreSQL COMMIT command is used to save all changes made during the current transaction. Once committed, these changes become permanent and visible to other users.
• If an error occurs during a transaction, you can use the ROLLBACK command to undo all changes made in the transaction.
• Changes made within a transaction are not visible to other sessions until COMMIT is executed.

dadimadhav

Follow

Improve