[分享]PHP的PgSQL数据库调用

引言:
PHP 调用 PostgreSQL 数据库主要通过 pgsql 扩展和 PDO_PGSQL 驱动实现。本文系统讲解环境配置、两种连接方式、SQL 语句执行、预处理语句防护 SQL 注入、事务处理、错误处理机制及 JSON/UUID 等 PostgreSQL 特有数据类型操作，帮助 PHP 开发者安全高效地连接和使用 PostgreSQL 数据库。

PHP 的 PostgreSQL 数据库调用完全指南

PostgreSQL 是一种功能强大的开源关系型数据库，与 PHP 的配合在现代 Web 开发中非常常见。相比其他数据库，PostgreSQL 在复杂查询、数据完整性和高级数据类型支持方面有着独特优势。PHP 连接 PostgreSQL 主要通过两种官方扩展来实现：pgsql 扩展和 PDO_PGSQL 驱动。本文将系统讲解从环境配置到连接操作，再到数据增删改查和安全实践的完整流程。

一、环境准备：安装 PostgreSQL 扩展

在开始编码之前，需要先确保 PHP 环境已经安装了连接 PostgreSQL 所需的扩展。

各平台安装方式

Ubuntu/Debian 系统：

sudo apt-get install php-pgsql

安装完成后，pgsql 和 pdo_pgsql 扩展会自动启用。

CentOS/RHEL 系统：

sudo yum install php-pgsql

Windows 系统：
下载 PHP 后，在 php.ini 配置文件中取消 extension=php_pgsql.dll 和 extension=php_pdo_pgsql.dll 前的注释符号（分号），保存后重启 Web 服务器即可。

macOS 系统（使用 Homebrew）：

brew install php@8.2
brew install postgresql@15
# PHP 通常已包含 pdo_pgsql，若缺失则通过 pecl 安装
pecl install pdo_pgsql

通过源码编译安装

如果需要从源码编译 PHP 并包含 PostgreSQL 支持，可以在编译时添加以下选项：

./configure --with-pgsql[=DIR] --with-pdo-pgsql[=DIR]

其中 [=DIR] 是 PostgreSQL 的安装目录或 pg_config 的路径。

验证扩展是否安装成功

php -m | grep -i pgsql

正常输出应包含 pgsql 和 pdo_pgsql，表示扩展已成功加载。

二、两种连接方式详解

PHP 连接 PostgreSQL 主要有两种方式，各有侧重。

方式一：pgsql 扩展

pgsql 扩展是 PHP 专门为 PostgreSQL 设计的原生扩展，提供了一系列以 pg_ 为前缀的函数。它更专注于 PostgreSQL 的特性，适合只需要连接 PostgreSQL 且对性能有要求的项目。

连接参数说明：

参数	说明	默认值
host	数据库服务器地址	localhost
port	数据库端口	5432
dbname	数据库名称	用户名
user	数据库用户名	操作系统用户名
password	数据库密码	空
sslmode	SSL 模式（require/disable/prefer）	prefer

示例：使用 pgsql 扩展连接：

<?php
$host = "localhost";
$port = "5432";
$dbname = "mydatabase";
$user = "myuser";
$password = "mypassword";

$connection_string = "host=$host port=$port dbname=$dbname user=$user password=$password";
$dbconn = pg_connect($connection_string);

if ($dbconn) {
    echo "成功连接到 PostgreSQL 数据库！";
} else {
    echo "连接失败：" . pg_last_error();
}
?>

使用 SSL 连接：

<?php
$connection_string = "host=localhost port=5432 dbname=mydatabase user=myuser password=mypassword sslmode=require";
$dbconn = pg_connect($connection_string);
if (!$dbconn) {
    die("SSL 连接失败：" . pg_last_error());
}
?>

方式二：PDO_PGSQL 驱动

PDO（PHP Data Objects）提供了统一的数据库访问接口，使用 PDO_PGSQL 驱动可以让代码在多种数据库之间平滑切换，可移植性更好。

示例：使用 PDO_PGSQL 连接：

<?php
$dsn = "pgsql:host=localhost;port=5432;dbname=mydatabase";
$user = "myuser";
$password = "mypassword";

try {
    $pdo = new PDO($dsn, $user, $password);
    // 设置错误模式为异常
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // 设置默认提取模式
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    echo "成功连接到 PostgreSQL 数据库！";
} catch (PDOException $e) {
    die("连接失败：" . $e->getMessage());
}
?>

两种方式对比

特性	pgsql 扩展	PDO_PGSQL
数据库支持	仅 PostgreSQL	支持 12+ 种数据库
接口风格	函数式	面向对象
预处理语句	支持（pg_prepare/pg_execute）	支持（prepare/execute）
命名参数	不支持（仅位置占位符 $1, $2）	支持命名参数 :name
异常处理	手动检查 pg_last_error()	原生支持 try-catch
适用场景	PostgreSQL 专属高性能项目	跨数据库项目或需要统一接口

三、核心数据库操作

查询数据（SELECT）

pgsql 扩展查询示例：

<?php
$dbconn = pg_connect("host=localhost dbname=mydatabase user=myuser password=mypassword");

if (!$dbconn) {
    die("连接失败：" . pg_last_error());
}

$query = "SELECT id, name, quantity FROM inventory ORDER BY id";
$result = pg_query($dbconn, $query);

if (!$result) {
    die("查询失败：" . pg_last_error($dbconn));
}

// 使用 pg_fetch_assoc() 获取关联数组
while ($row = pg_fetch_assoc($result)) {
    echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Quantity: " . $row['quantity'] . "<br>";
}

// 使用 pg_fetch_object() 获取对象
while ($row = pg_fetch_object($result)) {
    echo "ID: " . $row->id . " - Name: " . $row->name . " - Quantity: " . $row->quantity . "<br>";
}

// 使用 pg_fetch_row() 获取索引数组
while ($row = pg_fetch_row($result)) {
    echo "Data row = (" . implode(", ", $row) . ") <br>";
}

pg_free_result($result);
pg_close($dbconn);
?>

PDO 查询示例：

<?php
try {
    $pdo = new PDO("pgsql:host=localhost;dbname=mydatabase", "myuser", "mypassword");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $sql = "SELECT id, name, quantity FROM inventory ORDER BY id";
    $stmt = $pdo->query($sql);
    
    // 使用 fetchAll() 一次性获取所有结果
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($rows as $row) {
        echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Quantity: " . $row['quantity'] . "<br>";
    }
    
    // 或者逐行获取
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Quantity: " . $row['quantity'] . "<br>";
    }
} catch (PDOException $e) {
    echo "查询失败：" . $e->getMessage();
}
?>

插入数据（INSERT）

使用 pgsql 扩展插入：

<?php
$dbconn = pg_connect("host=localhost dbname=mydatabase user=myuser password=mypassword");

if (!$dbconn) {
    die("连接失败：" . pg_last_error());
}

$name = "banana";
$quantity = 150;
$query = "INSERT INTO inventory (name, quantity) VALUES ('$name', $quantity)";
$result = pg_query($dbconn, $query);

if ($result) {
    echo "数据插入成功";
} else {
    echo "插入失败：" . pg_last_error($dbconn);
}

pg_close($dbconn);
?>

使用 PDO 插入：

<?php
try {
    $pdo = new PDO("pgsql:host=localhost;dbname=mydatabase", "myuser", "mypassword");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $sql = "INSERT INTO inventory (name, quantity) VALUES ('banana', 150)";
    $pdo->exec($sql);
    
    // 获取最后插入的 ID（PostgreSQL 需要使用 RETURNING 子句或序列）
    $lastId = $pdo->lastInsertId('inventory_id_seq');
    echo "数据插入成功，ID: " . $lastId;
} catch (PDOException $e) {
    echo "插入失败：" . $e->getMessage();
}
?>

更新数据（UPDATE）

<?php
// pgsql 扩展更新示例
$new_quantity = 200;
$name = 'banana';
$query = "UPDATE inventory SET quantity = $new_quantity WHERE name = '$name'";
$result = pg_query($dbconn, $query);

if ($result) {
    $affected = pg_affected_rows($result);
    echo "更新成功，影响行数：" . $affected;
} else {
    echo "更新失败：" . pg_last_error($dbconn);
}

// PDO 更新示例
$sql = "UPDATE inventory SET quantity = 200 WHERE name = 'banana'";
$rows = $pdo->exec($sql);
echo "更新成功，影响行数：" . $rows;
?>

删除数据（DELETE）

<?php
// pgsql 扩展删除示例
$query = "DELETE FROM inventory WHERE name = 'orange'";
$result = pg_query($dbconn, $query);

if ($result) {
    $affected = pg_affected_rows($result);
    echo "删除成功，影响行数：" . $affected;
} else {
    echo "删除失败：" . pg_last_error($dbconn);
}

// PDO 删除示例
$sql = "DELETE FROM inventory WHERE name = 'orange'";
$rows = $pdo->exec($sql);
echo "删除成功，影响行数：" . $rows;
?>

四、预处理语句与 SQL 注入防护

预处理语句是防御 SQL 注入攻击的标准做法，使用时应严格遵守。

pgsql 扩展中的预处理语句

<?php
$dbconn = pg_connect("host=localhost dbname=mydatabase user=myuser password=mypassword");

if (!$dbconn) {
    die("连接失败：" . pg_last_error());
}

// 使用 pg_prepare 创建预处理语句（$1, $2 是位置占位符）
$result = pg_prepare($dbconn, "insert_query", "INSERT INTO inventory (name, quantity) VALUES ($1, $2)");

if (!$result) {
    die("预处理失败：" . pg_last_error($dbconn));
}

// 执行预处理语句
$result = pg_execute($dbconn, "insert_query", array("banana", 150));

if ($result) {
    echo "数据插入成功";
} else {
    echo "插入失败：" . pg_last_error($dbconn);
}

// 查询预处理
$result = pg_prepare($dbconn, "select_query", "SELECT id, name, quantity FROM inventory WHERE quantity > $1");
$result = pg_execute($dbconn, "select_query", array(100));

while ($row = pg_fetch_assoc($result)) {
    echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Quantity: " . $row['quantity'] . "<br>";
}

pg_free_result($result);
pg_close($dbconn);
?>

PDO 预处理语句

<?php
try {
    $pdo = new PDO("pgsql:host=localhost;dbname=mydatabase", "myuser", "mypassword");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 使用命名参数（推荐，可读性更好）
    $stmt = $pdo->prepare("INSERT INTO inventory (name, quantity) VALUES (:name, :quantity)");
    $stmt->execute([':name' => 'banana', ':quantity' => 150]);
    echo "插入成功，ID: " . $pdo->lastInsertId('inventory_id_seq');
    
    // 使用位置占位符
    $stmt = $pdo->prepare("INSERT INTO inventory (name, quantity) VALUES (?, ?)");
    $stmt->execute(['apple', 100]);
    echo "插入成功";
    
    // 查询预处理
    $stmt = $pdo->prepare("SELECT id, name, quantity FROM inventory WHERE quantity > :minQty");
    $stmt->execute([':minQty' => 50]);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Quantity: " . $row['quantity'] . "<br>";
    }
} catch (PDOException $e) {
    echo "操作失败：" . $e->getMessage();
}
?>

五、事务处理

PostgreSQL 支持完整的 ACID 事务，PHP 中也可以很好地管理事务操作。

pgsql 扩展事务处理

<?php
$dbconn = pg_connect("host=localhost dbname=mydatabase user=myuser password=mypassword");

if (!$dbconn) {
    die("连接失败：" . pg_last_error());
}

// 开始事务
pg_query($dbconn, "BEGIN");

try {
    pg_query($dbconn, "UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    pg_query($dbconn, "UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    
    // 提交事务
    pg_query($dbconn, "COMMIT");
    echo "事务提交成功";
} catch (Exception $e) {
    // 回滚事务
    pg_query($dbconn, "ROLLBACK");
    echo "事务回滚：" . $e->getMessage();
}

pg_close($dbconn);
?>

PDO 事务处理

<?php
try {
    $pdo = new PDO("pgsql:host=localhost;dbname=mydatabase", "myuser", "mypassword");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 开始事务
    $pdo->beginTransaction();
    
    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    
    // 提交事务
    $pdo->commit();
    echo "事务提交成功";
} catch (PDOException $e) {
    // 回滚事务
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "事务回滚：" . $e->getMessage();
}
?>

六、错误处理机制

pgsql 扩展错误处理

<?php
$dbconn = pg_connect("host=localhost dbname=mydatabase user=myuser password=mypassword");

if (!$dbconn) {
    die("连接失败：" . pg_last_error());
}

$result = pg_query($dbconn, "SELECT * FROM non_existent_table");
if (!$result) {
    // 获取详细的错误信息
    $error = pg_last_error($dbconn);
    $errorCode = pg_result_error_field($result, PGSQL_DIAG_SQLSTATE);
    echo "错误代码: $errorCode<br>";
    echo "错误信息: $error<br>";
} else {
    pg_free_result($result);
}

// 获取当前连接的最后错误
if (pg_last_error($dbconn)) {
    echo "最近操作发生错误：" . pg_last_error($dbconn);
}

pg_close($dbconn);
?>

PDO 错误处理

<?php
try {
    $pdo = new PDO("pgsql:host=localhost;dbname=mydatabase", "myuser", "mypassword");
    // 设置错误模式为异常
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->query("SELECT * FROM non_existent_table");
} catch (PDOException $e) {
    echo "错误代码: " . $e->getCode() . "<br>";
    echo "错误信息: " . $e->getMessage() . "<br>";
    echo "错误文件: " . $e->getFile() . "<br>";
    echo "错误行号: " . $e->getLine() . "<br>";
}
?>

七、连接池与持久连接

pgsql 扩展持久连接

<?php
// 使用 PGSQL_CONNECT_FORCE_NEW 强制建立新连接
$dbconn = pg_connect("host=localhost dbname=mydatabase user=myuser password=mypassword", PGSQL_CONNECT_FORCE_NEW);

// 使用 pg_pconnect() 建立持久连接
$dbconn = pg_pconnect("host=localhost dbname=mydatabase user=myuser password=mypassword");
if ($dbconn) {
    echo "持久连接建立成功";
} else {
    echo "连接失败：" . pg_last_error();
}
?>

PDO 持久连接

<?php
// 在 DSN 中添加 persist 参数
$dsn = "pgsql:host=localhost;dbname=mydatabase;persist=true";
try {
    $pdo = new PDO($dsn, "myuser", "mypassword");
    echo "持久连接建立成功";
} catch (PDOException $e) {
    echo "连接失败：" . $e->getMessage();
}
?>

八、操作 PostgreSQL 特有数据类型

JSON/JSONB 数据类型操作

<?php
// 插入 JSON 数据
$jsonData = json_encode(['name' => 'product1', 'tags' => ['electronics', 'sale']]);
$query = "INSERT INTO products (name, metadata) VALUES ('Product A', '$jsonData')";

// 查询 JSON 数据
$query = "SELECT metadata->>'name' as meta_name FROM products";
$result = pg_query($dbconn, $query);
while ($row = pg_fetch_assoc($result)) {
    echo "元数据中的名称: " . $row['meta_name'] . "<br>";
}

// PDO 方式
$stmt = $pdo->prepare("SELECT metadata->>'name' as meta_name FROM products");
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "元数据中的名称: " . $row['meta_name'] . "<br>";
}
?>

UUID 类型操作

<?php
// PostgreSQL 需要启用 uuid-ossp 扩展
// 执行: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

// 插入 UUID
$query = "INSERT INTO users (id, name) VALUES (gen_random_uuid(), 'John Doe')";

// 查询 UUID
$query = "SELECT id, name FROM users";
$result = pg_query($dbconn, $query);
while ($row = pg_fetch_assoc($result)) {
    echo "UUID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
}
?>

九、数据库性能优化建议

建议	说明
使用索引	为经常查询的列建立索引，注意避免过多索引影响写入性能
使用连接池	减少频繁创建和销毁连接的开销
批量操作	使用 INSERT INTO ... VALUES (...), (...), (...) 或 COPY 命令批量导入
预处理语句	使用预处理语句提升重复查询的性能
合理设置连接超时	避免长时间等待无法响应的数据库连接
监控慢查询	利用 PostgreSQL 的 pg_stat_statements 扩展分析慢查询

十、完整的连接配置示例

<?php
class Database {
    private static ?PDO $instance = null;
    
    private function __construct() {}
    
    public static function getInstance(): PDO {
        if (self::$instance === null) {
            $config = [
                'host' => getenv('DB_HOST') ?: 'localhost',
                'port' => getenv('DB_PORT') ?: '5432',
                'dbname' => getenv('DB_NAME') ?: 'myapp',
                'user' => getenv('DB_USER') ?: 'appuser',
                'password' => getenv('DB_PASS') ?: '',
                'sslmode' => getenv('DB_SSL') ?: 'disable',
            ];
            
            $dsn = sprintf(
                "pgsql:host=%s;port=%s;dbname=%s;sslmode=%s",
                $config['host'],
                $config['port'],
                $config['dbname'],
                $config['sslmode']
            );
            
            try {
                self::$instance = new PDO($dsn, $config['user'], $config['password']);
                self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                self::$instance->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            } catch (PDOException $e) {
                die("数据库连接失败: " . $e->getMessage());
            }
        }
        return self::$instance;
    }
}

// 使用示例
$db = Database::getInstance();
$stmt = $db->query("SELECT * FROM inventory");
while ($row = $stmt->fetch()) {
    print_r($row);
}
?>

总结

PHP 与 PostgreSQL 的配合使用是现代 Web 开发中的常见实践，核心要点可归纳为：

1. 选择扩展：pgsql 扩展适合 PostgreSQL 专属高性能项目，PDO_PGSQL 适合跨数据库或需要更高抽象层的项目
2. 安全第一：始终使用预处理语句防止 SQL 注入，合理配置错误处理
3. 连接配置：密码避免硬编码，生产环境注意连接池和超时配置
4. 事务处理：使用事务保证数据一致性
5. 特有功能：利用 PostgreSQL 的 JSON、UUID 等高级数据类型

掌握这些基础操作后，可以进一步学习 PostgreSQL 的存储过程、全文搜索、高级索引和分区表等特性，构建更强大的 Web 应用。