How to Connect to PostgreSQL from PHP

PHP has two built-in ways to reach PostgreSQL: the database-agnostic PDO layer and the PostgreSQL-specific pgsql extension. For new code, PDO with the pdo_pgsql driver is the right default. It gives you the same API you would use for MySQL or SQLite, real prepared statements, and a clean exception model. The pgsql extension is still maintained and exposes a few Postgres-only features, but most applications never need it. This guide covers both, the connection string, prepared statements, pooling realities, transactions, SSL, and the errors you will meet.

Which extension to use

Two extensions ship with PHP:

• PDO with pdo_pgsql is the portable, object-oriented choice. Same interface across databases, exceptions on error, and named or positional placeholders. Use this unless you have a specific reason not to.
• pgsql (the functions starting with pg_, like pg_connect and pg_query) is the PostgreSQL-specific extension. It is marginally faster and exposes Postgres-only features such as COPY streaming and asynchronous queries. Reach for it only when you actually need those features.

Check that the driver is loaded:

php -m | grep pgsql
# expect: pdo_pgsql  (and/or pgsql)

On Debian/Ubuntu, apt install php-pgsql provides both. This guide is written against PHP 8.5 (8.5.7 as of June 2026), but everything except the driver-subclass note below works on 8.1+.

Connecting with PDO

<?php
$dsn = 'pgsql:host=localhost;port=5432;dbname=shop';
 
try {
    $pdo = new PDO($dsn, 'appuser', 'secret', [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
} catch (PDOException $e) {
    // Do not echo $e->getMessage() to users; it can leak the DSN
    error_log('DB connection failed: ' . $e->getMessage());
    throw $e;
}

Three options are worth setting on every connection:

• ERRMODE_EXCEPTION makes PDO throw on errors instead of returning false. Without it, a failed query looks like success until you check a return value, which almost nobody does consistently.
• FETCH_ASSOC returns rows as associative arrays instead of the default duplicated numeric-and-associative array.
• ATTR_EMULATE_PREPARES => false tells PDO to use real server-side prepared statements. For pdo_pgsql this is the honest default behavior; setting it explicitly documents intent and avoids client-side emulation surprises.

Prepared statements

Never interpolate user input into SQL. Use placeholders and let the driver bind them:

$stmt = $pdo->prepare('SELECT id, name FROM products WHERE price > :minPrice');
$stmt->execute(['minPrice' => 100]);
 
foreach ($stmt as $row) {
    echo "{$row['id']}: {$row['name']}\n";
}

PDO supports named placeholders (:minPrice) and positional ones (?). Pick one style per statement; you cannot mix them. Named placeholders are easier to read and reorder, which matters once a query has more than two or three parameters.

Getting a generated key

PostgreSQL identity and serial columns do not return their value automatically. The clean way is RETURNING:

$stmt = $pdo->prepare(
    'INSERT INTO products (name, price) VALUES (:name, :price) RETURNING id'
);
$stmt->execute(['name' => 'Widget', 'price' => 9.99]);
$newId = $stmt->fetchColumn();

PDO::lastInsertId() also works but relies on sequence naming conventions and is less explicit. RETURNING is portable across PostgreSQL versions and lets you read back any column the insert generated, not just the key.

The PDO driver subclasses (PHP 8.4+)

PHP 8.4 added driver-specific PDO subclasses: Pdo\Pgsql, Pdo\Mysql, Pdo\Sqlite, and others. Constructing a connection through Pdo\Pgsql::connect() gives you a typed object that exposes Postgres-only helpers such as pgsqlCopyFromArray() and pgsqlGetNotify() as first-class methods:

// PHP 8.4+
$pdo = Pdo\Pgsql::connect($dsn, 'appuser', 'secret');

In PHP 8.5, calling those Postgres-specific methods on a plain PDO instance is deprecated in favor of the subclass. If you are on 8.4 or later and use any Postgres-specific PDO method, switch to Pdo\Pgsql. If you only run portable SQL, plain PDO is still fine.

Transactions

$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE accounts SET balance = balance - :amt WHERE id = :id')
        ->execute(['amt' => 50, 'id' => 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + :amt WHERE id = :id')
        ->execute(['amt' => 50, 'id' => 2]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

PostgreSQL wraps each statement in an implicit transaction by default, so an explicit transaction is only needed when several statements must succeed or fail together. One trap: after any error inside a transaction, PostgreSQL aborts the whole transaction and rejects further statements with current transaction is aborted. You must rollBack() (or roll back to a savepoint) before the connection is usable again.

Connection pooling

PHP's request-per-process model means a fresh connection is opened and torn down on most requests, which is wasteful under load. There are two answers, and the better one is not in PHP:

• PDO::ATTR_PERSISTENT => true keeps the connection open across requests within the same PHP worker. It helps, but it has sharp edges: a connection left in a bad state (open transaction, temp tables, session settings) is reused by the next request. Use it with care, and never leave transactions open.
• An external pooler such as PgBouncer is the production-grade answer. It sits between PHP and PostgreSQL and multiplexes many short PHP connections onto a small pool of real backend connections. In transaction pooling mode, disable server-side prepared statement caching concerns by keeping statements short-lived, and avoid session-level features that span transactions.

For most real deployments, PgBouncer in front of PostgreSQL beats ATTR_PERSISTENT and is the standard pattern.

SSL

For connections over an untrusted network, request encryption and verify the server certificate via the DSN:

$dsn = 'pgsql:host=db.example.com;port=5432;dbname=shop;'
     . 'sslmode=verify-full;sslrootcert=/etc/ssl/certs/pg-ca.pem';

sslmode accepts the standard PostgreSQL values. require encrypts but does not validate the certificate, leaving you open to a man-in-the-middle. verify-full encrypts, validates the CA, and checks the hostname; use it for anything crossing a public network.

Using the pgsql extension directly

If you need Postgres-only features, the pg_* functions are there:

$conn = pg_connect('host=localhost dbname=shop user=appuser password=secret');
 
$result = pg_query_params(
    $conn,
    'SELECT id, name FROM products WHERE price > $1',
    [100]
);
 
while ($row = pg_fetch_assoc($result)) {
    echo "{$row['id']}: {$row['name']}\n";
}

Note the $1 positional placeholders, which differ from PDO's ? and :name. Always use pg_query_params rather than building SQL with pg_query and string concatenation, for the same injection-safety reason.

Errors you actually hit

For exploring PostgreSQL data and drafting queries before they go into PHP, Mako connects to PostgreSQL with AI-powered autocomplete. Try it free at mako.ai.