PostgreSQL – COMMIT Statement

The COMMIT statement is used to permanently save any changes made within a transaction to the PostgreSQL database. PostgreSQL, like many other relational database management systems, uses transactions to group a series of database operations into a single unit of work. The COMMIT statement is issued to confirm that the changes made within a transaction should be saved to the database.

Here’s how you typically use the COMMIT statement in PostgreSQL:

Start a transaction using the BEGIN statement:

BEGIN; 

This begins a new transaction.

Perform one or more SQL operations (e.g., INSERT, UPDATE, DELETE, etc.) within the transaction.

When you are satisfied with the changes made in the transaction and want to save them permanently, issue the COMMIT statement:

COMMIT; 

This will make the changes made within the transaction permanent and visible to other database sessions.

Here’s an example of a simple transaction:

BEGIN; 
-- Perform some operations within the transaction 

INSERT INTO employees (name, salary) VALUES ('John', 50000); 

UPDATE departments SET manager_id = 1 WHERE department_name = 'HR'; 

-- If everything is fine, commit the changes 
COMMIT;

In this example, a transaction is started with BEGIN, and then some database operations are performed. If everything goes well, the COMMIT statement is used to save those changes permanently.

If you want to discard the changes made within a transaction without saving them, you can use the ROLLBACK statement instead of COMMIT.

ROLLBACK;

This will undo all the changes made within the current transaction and effectively cancel it.

PostgreSQL also supports implicit transactions, which means that if you don’t explicitly start a transaction with BEGIN, each statement is treated as its own transaction and is auto-committed. However, for more complex operations or multiple related changes that should be treated as a single unit, it’s good practice to use explicit transactions with BEGIN, COMMIT, and ROLLBACK.