Perl and PostgreSQL – Inserting Data

In order to insert data into a PostgreSQL database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.

Inserting data works in a similar fashion as selecting data using parameters. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are 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 DateTime;
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 parameters.
    my $firstname = "Fiona";
    my $lastname = "Jones";
    my $title = "Miss";
    my $dob = DateTime->new(
        year => 1985,
        month => 5,
        day => 19,
        hour => 0,
        minute => 0,
        second => 0
    );

    # Query text.
    my $sqlText = " \
        INSERT INTO person \
               (firstname, lastname, title, dob)  \
        VALUES (?, ?, ?, ?) \
    ";

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

    # Execute the query.
    $sqlInsert->execute($firstname, $lastname, $title, $dob);

    # Confirm successful addition of person information.
    print "Person information added successfully.\n";

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

}
or do 
{

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

}

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

id	firstname	lastname	title	dob
1	Bob	Smith	Mr	1980-01-20
2	George	Jones	Mr	1997-12-15
3	Fred	Bloggs	Mr	1975-05-07
4	Alan	White	Mr	1989-03-20
5	Fiona	Jones	Miss	1985-05-19

Perl and PostgreSQL – Inserting Data

More