What is a transaction in PostgreSQL? Tell me about ACID.

In PostgreSQL, a transaction is a sequence of database operations that are executed as a single unit. Either all operations within the transaction are successfully applied, or none are (atomicity).

ACID is a set of properties that guarantee the correctness of transactions:

• Atomicity: All operations of a transaction are completed fully or not at all.
• Consistency: The transaction transforms the database from one valid state to another, adhering to all constraints.
• Isolation: Concurrent transactions do not interfere with each other, and the results of one transaction are not visible to others until it is completed.
• Durability: Once a transaction is committed, its changes are permanent even in case of failures.

Example of using a transaction in Go with PostgreSQL:

trx, err := db.Begin()
if err != nil {
    // handle error
}

_, err = trx.Exec("INSERT INTO accounts(user_id, balance) VALUES($1, $2)", userID, 100)
if err != nil {
    trx.Rollback()
    // handle error
}

err = trx.Commit()
if err != nil {
    // handle error
}

Here, all operations inside trx are either applied together or rolled back in case of an error.