How to Use PostgreSQL with PHP – Complete Beginner Guide

PostgreSQL and PHP have been a reliable combination for building dynamic web applications for many years. PHP is widely used for server-side development, while PostgreSQL offers a powerful, secure, and standards-compliant relational database.

In this guide, you’ll learn how to use PostgreSQL with PHP, covering connection setup, CRUD operations, prepared statements, transactions, error handling, and best practices for production environments.

Why Use PostgreSQL with PHP?

PHP remains one of the most popular backend languages for web development. When paired with PostgreSQL, it provides:

• Strong data integrity with ACID transactions
• High performance for read- and write-heavy workloads
• Advanced PostgreSQL features such as JSONB and indexing
• Excellent compatibility with modern PHP frameworks

This combination is commonly used in CMS platforms, APIs, and enterprise applications.

PostgreSQL Extensions for PHP

PHP provides two main ways to connect to PostgreSQL:

1. pgsql Extension

• Native PostgreSQL functions (pg_connect, pg_query)
• Simple and lightweight
• Less flexible for complex applications

2. PDO (PHP Data Objects) – Recommended

• Unified interface for multiple databases
• Supports prepared statements
• Better security and maintainability

In this tutorial, we focus on PDO, which is widely used in modern PHP applications.

Prerequisites

Before starting, make sure you have:

• PHP 8.1 or newer
• PostgreSQL installed and running
• PDO and PDO_PGSQL extensions enabled
• Basic knowledge of PHP and SQL

Enabling PostgreSQL Support in PHP

To verify PDO PostgreSQL support, run:

php -m | grep pgsql

If not installed, enable the extension in your php.ini file:

extension=pdo_pgsql
extension=pgsql

Restart your web server after making changes.

Connecting to PostgreSQL Using PDO

<?php
$dsn = "pgsql:host=localhost;port=5432;dbname=tutorialdb";
$user = "postgres";
$password = "yourpassword";

try {
    $pdo = new PDO($dsn, $user, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
    echo "Connected to PostgreSQL successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Key Benefits of PDO

• Consistent API
• Built-in protection against SQL injection
• Better error handling

Creating a Table Example

$sql = "
CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
";

$pdo->exec($sql);

Inserting Data into PostgreSQL

$sql = "INSERT INTO customers (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);

$stmt->execute([
    ':name' => 'John Doe',
    ':email' => 'john@example.com'
]);

Why Use Prepared Statements?

• Prevents SQL injection
• Improves security
• Handles data binding automatically

Selecting Data from PostgreSQL

$sql = "SELECT id, name, email FROM customers";
$stmt = $pdo->query($sql);

$customers = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($customers as $customer) {
    echo $customer['name'] . "<br>";
}

PDO fetch modes allow you to return results as arrays or objects.

Updating Data

$sql = "UPDATE customers SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);

$stmt->execute([
    ':email' => 'newemail@example.com',
    ':id' => 1
]);

Always check affected rows when performing critical updates.

Deleting Data

$sql = "DELETE FROM customers WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => 1]);

In production systems, consider soft deletes instead of permanent removal.

Using Transactions in PHP

try {
    $pdo->beginTransaction();

    $stmt = $pdo->prepare(
        "INSERT INTO customers (name, email) VALUES (:name, :email)"
    );
    $stmt->execute([
        ':name' => 'Alice',
        ':email' => 'alice@example.com'
    ]);

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Transactions ensure data consistency when multiple queries are executed together.

Error Handling Best Practices

PDO provides flexible error handling modes:

• PDO::ERRMODE_EXCEPTION (recommended)
• PDO::ERRMODE_WARNING
• PDO::ERRMODE_SILENT

Using exceptions makes debugging and logging much easier.

Performance Optimization Tips

• Use prepared statements for repeated queries
• Add indexes on frequently queried columns
• Avoid SELECT * on large tables
• Use pagination for large datasets
• Analyze queries with EXPLAIN ANALYZE

PostgreSQL with PHP Frameworks

PostgreSQL integrates well with popular PHP frameworks:

• Laravel (native PostgreSQL support)
• Symfony
• CodeIgniter
• Slim Framework

Most frameworks use PDO internally, making the transition seamless.

Security Best Practices

• Store database credentials in environment variables
• Use least-privilege database users
• Validate and sanitize user input
• Enable SSL connections in production
• Regularly update PHP and PostgreSQL versions

Common Use Cases

• Content Management Systems (CMS)
• RESTful APIs
• E-commerce platforms
• Enterprise web applications
• Data-driven dashboards

Conclusion

Using PostgreSQL with PHP is a proven and reliable approach for building secure and scalable web applications. With PDO, you can write clean, maintainable code while taking advantage of PostgreSQL’s powerful database features.

By following best practices for security, performance, and transaction handling, PHP and PostgreSQL can confidently support production-grade applications.