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.
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
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;
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 // commmiting the transaction SELECT customer_id, full_name, balance FROM BankStatements; UPDATE BankStatements SET balance = balance + 500 WHERE customer_id = 2; COMMIT; // displaying data after // commmiting the transaction SELECT customer_id, full_name, balance FROM BankStatements;
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.