Perl and PostgreSQL – Deleting Data

In order to delete data in a PostgreSQL database, the SQL ‘Delete’ statement needs to be used. The following example deletes a record with a specific ‘id’ from the ‘person’ table, which was used in the examples for selecting, inserting and updating data.

Deleting data works in a similar way to the other examples, where a parameterised query is used. Firstly, a connection to the database is established, then the query parameter for the ‘id’ of the record to be deleted is defined as a variable, which is bound in to the following SQL statement when it is executed. A confirmation message is displayed. The declaration and execution of the SQL statement is wrapped in an ‘eval-or-do’ block to catch any errors that may arise.

use strict;
use warnings;
use DBI;

# Database connection variable.
my $connect;

eval 
{

    # Connect to database.
    $connect = DBI->connect("DBI:Pg:dbname = demo; host = localhost; port = 5432", 
                            "DemoUN", "DemoPW", {RaiseError => 1});

} 
or do 
{

    # Message confirming unsuccessful database connection.
    print "Database connection unsuccessful.\n";

    # Stop program execution.
    exit(1);

};

eval
{

    # Query parameter.
    my $id = 2;

    # Query text.
    my $sqlText = " \
         DELETE FROM person \
         WHERE id = ? \
    ";

    # Prepare the query.
    my $sqlDelete = $connect->prepare($sqlText);

    # Execute the query.
    $sqlDelete->execute($id);

    # Confirm successful deletion of person information.
    print "Person information deleted successfully.\n";

    # Clean up.
    $sqlDelete->finish();
    $connect->disconnect();

}
or do
{

    # Confirm error deleting person information and exit.
    print "Error deleting person information.\n";
    exit(1);

}

The contents of the ‘person’ table now looks as follows.

More

• Introduction
• Selecting Data
• Inserting Data
• Updating Data
• Importing Data (CSV)
• Importing Data (Text)