ROLLBACK Statement - SQL Server to PostgreSQL Migration

In SQL Server and PostgreSQL, the ROLLBACK statement rolls back the current transaction.

SQL Server and PostgreSQL:

  -- Sample table
  CREATE TABLE colors (name VARCHAR(30));
 
  BEGIN TRANSACTION;
 
  -- Insert a sample row
  INSERT INTO colors VALUES('Black');
 
  ROLLBACK TRANSACTION;
 
  SELECT * FROM colors;
  /* No rows returned */

SQL Server allows you to rollback the transaction in a stored procedure:

SQL Server:

   -- Sample procedure
  CREATE PROCEDURE sp_add_color @name VARCHAR(30), @type CHAR(1)
  AS
    -- Insert a row
    INSERT INTO colors VALUES(@name);
 
    IF @type = 'C' 
      COMMIT TRANSACTION;
    ELSE
      ROLLBACK TRANSACTION;
  GO

In a PostgreSQL stored procedure, you can also rollback the current transaction, but you have to use the ROLLBACK statement, not ROLLBACK TRANSACTION (although the TRANSACTION keyword is allowed outside procedure).

PostgreSQL:

  -- Sample procedure
  CREATE OR REPLACE PROCEDURE sp_add_color(p_name VARCHAR(30), p_type CHAR(1))
  AS $$
  BEGIN 
    -- Insert a row
    INSERT INTO colors VALUES(p_name);
 
    IF p_type = 'C' THEN 
      COMMIT; 
    ELSE
      ROLLBACK;
    END IF;
  END;
  $$ LANGUAGE plpgsql;

For more information, see SQL Server to PostgreSQL Migration.