Definition of PostgreSQL COMMIT
PostgreSQL commit is used to save the transaction changes to the database, which the user made. The default value of commit is ON in PostgreSQL, which means we need not have to execute a commit statement to save the transaction; it will automatically save the transaction into the database. If we set auto-commit is off, then we need to write commit to save the transaction into the database. Commit is used to saves the transaction changes into the database. Commit is very important in PostgreSQL to save any changes which the user did.
Syntax:
Below is the syntax of a commit in PostgreSQL, which are as follows.
COMMIT [ WORK (Optional keywords in commit) | TRANSACTION (Optional keywords in commit) ]
The transaction commits using begin and end statements.
Begin
Statement1 …., Statement N
End transaction;
Parameter:
- Commit: Commit is used in PostgreSQL to save any changes in the database, which the user made. Commit is very important in PostgreSQL to save changes.
- Work: Work is an optional keyword in a commit. We can use it as “Commit Work”, which means that we save the work into the database.
- Transaction: Transaction is an optional keyword in a commit. We can use it as “Commit Transaction”, which means that we save the transaction into the database.
- Begin: Begin is used in PostgreSQL to start the transaction. After starting a transaction, we end the same and then we commit this transaction into the database.
- Statement 1 to statement N: This is the statement used to save the result into the database. We can use multiple statements at one time.
- End transaction: The end transaction statement is used to end the current running transaction. After executing an end transaction, changes that were made by the user automatically save into the database.
How to COMMIT Works in PostgreSQL?
- By default, auto-commit is on in PostgreSQL below figure shows the auto-commit transaction is ON in PostgreSQL.
- We have to check the default value by using the “\echo :autocommit” command.
Output:
- We have set autocommit on by using the following command at the session-level are as follows.
testing=# \set AUTOCOMMIT on
testing=# \echo :AUTOCOMMIT;
Output:
- We have set autocommit OFF by using the following command at the session-level are as follows.
testing=# \set AUTOCOMMIT OFF
testing=# \echo :AUTOCOMMIT;
Output:
- We can also commit the transaction using begin and end statements in PostgreSQL. Begin and end statement is also used to commit the transaction.
- Commit is used to save a single transaction commit and also used to save multiple transactions at one time.
- Suppose we used to begin and end transaction command. In that case, a commit will show a warning message that “there is no transaction in progress” because this transaction was already saved into the database. Currently, there was no transaction to commit or save to the database.
- Commit in PostgreSQL is very important to save single or multiple transactions into the database at one time.
- If we set auto commit is ON in PostgreSQL, we need not write commit statements at every statement. It will automatically commit the transaction after every transaction. It will automatically save the transaction into the database.
- If we set auto commit is off, then we need to execute the commit statement after single or multiple executions of the transaction to save the transaction into the database.
Examples to Implement COMMIT in PostgreSQL
We are giving examples of multiple transactions commit and single transaction commit.
1. Single Transaction COMMIT
- In the below example, we have taken an example of a single transaction commit.
- We have to create an emp_test table, and after creating a table, we have saved the transaction into the database by using commit.
- Below is the example of a single transaction commit are as follows.
Example
testing=# \set AUTOCOMMIT off
testing=# CREATE TABLE Emp_Test (emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);
testing=# commit;
Output:
- In PostgreSQL, by default, the auto commit is one of every transaction, so we need to set it off if we want to save the transaction later.
- In the above example, we have saved the create table transaction into the database.
2. Multiple Transaction COMMIT (Using begin and end statement)
- In the below example, we have taken an example of multiple transaction commit.
- We have to create an emp_test1 table; after creating a table, we have inserting some value into this table then we have saved the transaction into the database by using an end statement.
- Below is the example of multiple transaction commit are as follows.
Example
testing=# \set AUTOCOMMIT off
testing=# Begin;
testing=# CREATE TABLE Emp_Test1 (emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);
testing=# INSERT INTO Emp_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');
testing=# INSERT INTO Emp_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (2, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');
testing=# INSERT INTO Emp_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (3, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');
testing=# End transaction;
testing=# Commit;
Output:
- We can also commit the transaction using begin and end statements in PostgreSQL. Begin and end statement is also used to commit the transaction.
- The above example shows that we have commit multiple transactions using begin and end statements in PostgreSQL.
- After the end transaction, if we execute commit, it will show a warning message that “there is no transaction in progress” because this transaction was already saved into the database, and currently, there was no transaction pending to commit or save into the database.
Conclusion
PostgreSQL commit is used to save the transaction into the database. We can also use begin and end statements to save the transaction into the database. By default, auto commit transaction is ON in PostgreSQL. Commit is very important in PostgreSQL to save the transaction into the database.
Recommended Articles
This is a guide to PostgreSQL COMMIT. Here we discuss the definition and working of PostgreSQL commit along with different examples and its code implementation. You may also look at the following articles to learn more –
