PostgreSQL – COMMIT

PostgreSQL COMMIT command is used to save changes and reflect it database whenever we display the required data. For suppose we updated data in the database but we didn’t give COMMIT then the changes are not reflected in the database. To save the changes done in a transaction, we should COMMIT that transaction for sure.

Syntax :

COMMIT TRANSACTION;

(or)

COMMIT;

(or)

END TRANSACTION;

Unlike other database languages in PostgreSQL, we commit the transaction in 3 different forms which are mentioned above. Now for getting good command in 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
);

Now we will insert data of some customers

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: 

We will add the data to the table in the transaction using the commit

BEGIN;

 INSERT INTO BankStatements (
     customer_id,
    full_name,
        balance

)
    VALUES(
       4, 'Priya chetri', 500
        )
;
        
COMMIT;

Output:

Example 2:

We will update the balance and display the data without committing the transaction thereafter committing it.

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:

NOTE: When we try to display data from another session before committing the changes then we will get the same output that we got in the first example.