PostgreSQL
suggest changePHP 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);
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents