Connecting PHP to PostgreSQL with PDO

Use the pdo_pgsql driver and pgsql: DSN to connect PHP to PostgreSQL.

The examples focus on the operation itself, including the important distinction: pdo_mysql uses a mysql: DSN, not pgsql:.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

Requires pdo_pgsql; PostgreSQL-specific SQL syntax should not be confused with MySQL.

<?php
$pdo = new PDO(
    "pgsql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo"),
    getenv("DB_USER") ?: "app", getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$stmt = $pdo->query("SELECT current_database()");
echo $stmt->fetchColumn();

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

Requires pdo_pgsql; PostgreSQL-specific SQL syntax should not be confused with MySQL. A successful connection supports both introspection and SQL.

<?php
$pdo = new PDO(
    "pgsql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo"),
    getenv("DB_USER") ?: "app", getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$stmt = $pdo->query("SELECT current_database()");
echo $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

Requires pdo_pgsql; PostgreSQL-specific SQL syntax should not be confused with MySQL. A successful connection supports both introspection and SQL.

<?php
$pdo = new PDO(
    "pgsql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo"),
    getenv("DB_USER") ?: "app", getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$stmt = $pdo->query("SELECT current_database()");
echo json_encode($stmt->fetch(PDO::FETCH_ASSOC));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

PostgreSQL uses different parameter and SQL syntax details than MySQL; keep driver portability assumptions explicit.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Use the pdo_pgsql driver and pgsql: DSN to connect PHP to PostgreSQL.
• PostgreSQL uses different parameter and SQL syntax details than MySQL; keep driver portability assumptions explicit.
• pdo_mysql uses a mysql: DSN, not pgsql:.

Use Connecting PHP to PostgreSQL with PDO when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.