How to connect to PostgreSQL database using Perl DBI?

Connecting to a PostgreSQL database in Perl requires the DBI (Database Independent Interface) module along with the DBD::Pg driver. The DBI module provides a standardized interface for database operations across different database systems.

Basic Connection Syntax

To connect to PostgreSQL, use the DBI->connect() method with a Data Source Name (DSN) that specifies the driver, database name, host, and port. The basic format is:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# Simulate a PostgreSQL connection demonstration
# In production, you would use:
# my $dsn = "dbi:Pg:dbname=mydb;host=localhost;port=5432";
# my $username = "myuser";
# my $password = "mypassword";
# my $dbh = DBI->connect($dsn, $username, $password, {
#     RaiseError => 1,
#     AutoCommit => 1,
#     PrintError => 0
# }) or die "Connection failed: " . $DBI::errstr;

# Since we can't actually connect without PostgreSQL, 
# we'll demonstrate the connection string format
print "PostgreSQL Connection Guide\n";
print "=" x 40 . "\n\n";

print "DSN Format:\n";
print "  dbi:Pg:dbname=DATABASE;host=HOST;port=PORT\n\n";

print "Example Connection Code:\n";
print q{
  my $dbh = DBI->connect(
      "dbi:Pg:dbname=mydb;host=localhost;port=5432",
      "username",
      "password",
      {
          RaiseError => 1,
          AutoCommit => 1,
          PrintError => 0
      }
  );
};
print "\n\nConnection attributes explained:\n";
print "  RaiseError => 1  : Die on errors\n";
print "  AutoCommit => 1  : Commit each statement automatically\n";
print "  PrintError => 0  : Don't print warnings to STDERR\n";
print "\nAlways disconnect when done:\n";
print "  \$dbh->disconnect();\n";

Important Connection Options

• RaiseError: Set to 1 to automatically die on database errors (recommended)
• AutoCommit: Set to 1 for automatic commits, 0 for manual transaction control
• PrintError: Set to 0 to suppress automatic error printing when RaiseError is enabled
• pg_enable_utf8: DBD::Pg specific option to enable UTF-8 encoding

Common Pitfalls

Always remember to call $dbh->disconnect() when finished, though it will happen automatically when the handle goes out of scope. Check for connection errors using $DBI::errstr if not using RaiseError. The DSN format is case-sensitive for the driver name (must be "Pg" not "pg"). PostgreSQL uses port 5432 by default, but this can vary.

Note: Both DBI and DBD::Pg must be installed. DBI is a core module in many Perl distributions, but DBD::Pg typically requires separate installation via CPAN.