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
    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

          6          4.0088e+05          1004             8          "Sail Boat"    
          3            4.01e+05          1009            17          "Slinky"       
         10          8.8865e+05          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
    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

          6          4.0088e+05          1004             8         "Sail Boat"     
          3            4.01e+05          1009            17         "Slinky"        
         10          8.8865e+05          1006            24         "Teddy Bear"    
         30               5e+05          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
    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

          6          4.0088e+05          1004             8          "Sail Boat"    
          3            4.01e+05          1009            17          "Slinky"       
         10          8.8865e+05          1006            24          "Teddy Bear"   

Close Database Connection

close(conn)