PostgreSQL

suggest change

PHP provides two primary ways to connect to a PostgreSQL database: the database-agnostic PDO (pdo_pgsql) extension (recommended) and the native PostgreSQL (pgsql) extension.

Connecting with PDO

PDO is the standard, modern interface for database access in PHP. Ensure the pdo_pgsql extension is enabled in your php.ini.

$host = 'localhost';
$port = 5432;
$db   = 'app_db';
$user = 'postgres';
$pass = 'secret';

$dsn = "pgsql:host=$host;port=$port;dbname=$db";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected to PostgreSQL successfully!\n";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage() . "\n";
}

Parameterized Queries and Insertions

Always use prepared statements with placeholders to protect against SQL injection:

// Inserting data and using PostgreSQL's RETURNING clause
$stmt = $pdo->prepare(
    'INSERT INTO users (username, email, created_at)
     VALUES (:username, :email, NOW())
     RETURNING id'
);

$stmt->execute([
    'username' => 'alice',
    'email'    => 'alice@example.com',
]);

$newUserId = $stmt->fetchColumn();
echo "Inserted user with ID: $newUserId\n";

Querying Records

$stmt = $pdo->prepare('SELECT id, username, email FROM users WHERE id = :id');
$stmt->execute(['id' => $newUserId]);

$user = $stmt->fetch();
if ($user) {
    echo "User: " . $user['username'] . " (" . $user['email'] . ")\n";
}

Transactions

PostgreSQL fully supports ACID transactions. Wrap multi-step modifications inside a transaction:

try {
    $pdo->beginTransaction();

    $stmt1 = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :from_id');
    $stmt1->execute(['amount' => 100, 'from_id' => 1]);

    $stmt2 = $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :to_id');
    $stmt2->execute(['amount' => 100, 'to_id' => 2]);

    $pdo->commit();
    echo "Transfer successful.\n";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction rolled back: " . $e->getMessage() . "\n";
}

Working with JSON / JSONB

PostgreSQL excels at handling structured JSON. You can query and store JSONB data directly:

// Insert JSON payload
$stmt = $pdo->prepare('INSERT INTO events (payload) VALUES (:payload::jsonb)');
$payload = json_encode(['action' => 'login', 'ip' => '192.168.1.1']);
$stmt->execute(['payload' => $payload]);

// Query within JSONB fields using PostgreSQL operators
$stmt = $pdo->prepare("SELECT payload->>'action' AS action FROM events WHERE payload->>'ip' = :ip");
$stmt->execute(['ip' => '192.168.1.1']);

$results = $stmt->fetchAll();
foreach ($results as $row) {
    echo "Event action: " . $row['action'] . "\n";
}

Using Native pg_* Functions

If working with legacy codebases that do not use PDO, the pgsql procedural functions can be used:

$conn = pg_connect("host=localhost port=5432 dbname=app_db user=postgres password=secret");

if (!$conn) {
    // pg_last_error() needs an open connection, so read the warning instead
    die("Connection failed: " . error_get_last()['message']);
}

$result = pg_query_params(
    $conn,
    'SELECT id, username FROM users WHERE email = $1',
    ['alice@example.com']
);

while ($row = pg_fetch_assoc($result)) {
    echo $row['username'] . "\n";
}

pg_free_result($result);
pg_close($conn);

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents