Roll Back Data in Database Using PostgreSQL Native Interface

This example shows how to connect to a database, insert a row into an existing database table, and then roll back (or reverse) the insert using the PostgreSQL native interface. The example uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12. The database contains the table productTable.

Create Database Connection

Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

Append Data to Existing Database Table

Set the AutoCommit property of the connection object to off. Any updates you make after turning off this flag are not committed to the database automatically.

conn.AutoCommit = "off";

To view the existing database table productTable before appending data, import its contents into MATLAB® and display the last few rows.

tablename = "productTable";
rows = sqlread(conn,tablename);
tail(rows,3)

ans=3×5 table
     6    400876    1004     8     "Sail Boat"
     3    400999    1009    17        "Slinky"
    10    888652    1006    24    "Teddy Bear"

Create a MATLAB table that contains the data for one product.

data = table(30,500000,1000,25,"Rubik's Cube", ...
    'VariableNames',["productnumber" "stocknumber" ...
    "suppliernumber" "unitcost" "productdescription"]);

Append the product data into the database table productTable.

sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB again, and display the last few rows. The results contain a new row for the inserted product.

rows = sqlread(conn,tablename);
tail(rows,4)

ans=4×5 table
     6    400876    1004     8       "Sail Boat"
     3    400999    1009    17          "Slinky"
    10    888652    1006    24      "Teddy Bear"
    30    500000    1000    25    "Rubik's Cube"

Roll Back Data

Roll back the inserted row.

rollback(conn)

Import the contents of the database table into MATLAB again, and display the last few rows. The results no longer contain the inserted row.

rows = sqlread(conn,tablename);
tail(rows,3)

ans=3×5 table
     6    400876    1004     8     "Sail Boat"
     3    400999    1009    17        "Slinky"
    10    888652    1006    24    "Teddy Bear"

Close Database Connection

close(conn)