Delete Data from Database Using PostgreSQL Native Interface

This example shows how to delete data from a PostgreSQL database using MATLAB®. First, create an SQL statement with the deletion SQL syntax. Consult your database documentation for the correct syntax. Execute the delete operation on your database using the execute function with the SQL statement.

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);

The SQL query sqlquery selects all rows of data in the table inventorytable. Execute this SQL query using the database connection. Import the data from the executed query using the fetch function, and display the last few rows.

sqlquery = "SELECT * FROM inventorytable";
data = fetch(conn,sqlquery);
tail(data,3)

ans=3×4 table
    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

         11             567         0     "2012-09-11 00:30:24"
         12            1278         0     "2010-10-29 18:17:47"
         13            1700      14.5     "2009-05-24 10:58:59"

Delete Specific Record

Delete the record for the product number 13 from the table inventorytable. Specify the product number using the WHERE clause in the SQL statement sqlquery.

sqlquery = "DELETE FROM inventorytable WHERE productnumber = 13";
execute(conn,sqlquery)

Display the data in the table inventorytable after the deletion. The record with product number 13 is no longer included.

sqlquery = "SELECT * FROM inventorytable";
data = fetch(conn,sqlquery);
tail(data,3)

ans=3×4 table
    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

         10             723       24      "2012-03-14 13:13:09"
         11             567        0      "2012-09-11 00:30:24"
         12            1278        0      "2010-10-29 18:17:47"

Close Database Connection

close(conn)