PostgreSQL - COMMIT

In PostgreSQL, COMMIT is used for saving all the changes made by the current transaction. Once you run the COMMIT statement, all the changes you made (like adding, updating, or deleting data) since the last COMMIT or ROLLBACK are permanently saved in the database. After that, those changes cannot be removed.

Syntax

Following is the syntax of COMMIT statement in PostgreSQL −

COMMIT [ WORK | TRANSACTION ]

Here,

• WORK and TRANSACTION are optional keywords that can be omitted. They are just used for readability.

How COMMIT Works in Transactions?

To undertand its usage in transaction remember the below points −

i. Transaction starts with BEGIN or START TRANSACTION.

ii. Mostly SQL statement are executed to resolve the transaction issues.

iii. If everything is successful, COMMIT is required to save the changes permanently.

iv. If an error occurs then ROLLBACK will be used to undo the changes.

Types of COMMIT in PostgreSQL

There are two types of commit in PostgreSQL −

Implicit Commit

In PostgreSQL, an implicit commit happens automatically when you run certain commands like CREATE TABLE or ALTER TABLE. These commands save changes to the database right away, without needing user to manually use COMMIT.

Explicit Commit

In PostgreSQL, an explicit commit happens when user manually use COMMIT to save changes. You can easily control when the changes are finalized.

Syntax

Following is the syntax of explicit commit in postgresql −

BEGIN;
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
COMMIT;

Example of COMMIT in PostgreSQL

Here, we have shown a employees table with 6 columns and added values in its correspondence and will teach you how to perform commit in the specific role. Below is given table −

Now, you get the practical exposure to learn the usage of COMMIT in PostgreSQL −

To get the above table use the SELECT statement.

SELECT * FROM employees;

Let us add another user to the employees table. Also, we know that once a COMMIT is executed, the database cannot return to its previous state.

So, we add one user to the existing table using INSERT INTO statement −

BEGIN;
INSERT INTO employees (id, name, position, salary, department, hire_date)
VALUES (6, 'Salman', 'QA Engineer', 68000.00, 'Engineering', '2023-09-15');
COMMIT;

By executing the above queries, we observed that row number 6 was added to the existing table and permanently saved. The output is given below −

These values are permanently inserted to the existing table.

Implicit Commit vs Explicit Commit in PostgreSQL

In this section, we will explore the differences of two different commits −

In the next chapter, we will provide you a detailed explanation of ROLLBACK in transactions.