Search code examples
postgresql

Do postgres use implicit transaction in UPDATE ... RETURNING queries?


Can someone let me know if Postgres 15 uses implicit transactions for UPDATE...RETURNING queries?

For example, is there any differences between the two queries

BEGIN;
UPDATE counter
SET value = value + 1
RETURNING value;
COMMIT;

and

UPDATE counter
SET value = value + 1
RETURNING value;

Table is like below

CREATE TABLE IF NOT EXISTS counter
(
    value      bigint PRIMARY KEY NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ        NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Solution

  • From de documentation (Page 19), you can read:

    PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block

    This means that all transactions including UPDATE will be treated as transactions without having to explicitly specify them.