第52课：PostgreSQL 操作

课程信息

所属模块：数据库
难度：Intermediate
标签：postgres, pgx, lib/pq, transaction
上一课：database/sql 基础
下一课：MySQL 操作

1. 学习目标

完成本课学习后，你将能够：

理解 Go 操作 PostgreSQL 的主流驱动（lib/pq 与 pgx）及其区别。
配置并建立与 PostgreSQL 数据库的安全连接。
使用 Go 执行标准的数据库 CRUD（增删改查）操作。
掌握在 Go 中使用 database/sql 管理数据库事务的方法。

2. 核心概念

选择哪个驱动？

在 Go 的 database/sql 生态中，主要有两个 PostgreSQL 驱动：

lib/pq: 老牌、稳定，完全兼容 database/sql 接口，社区成熟。
pgx: 性能更高，功能更丰富，支持 database/sql 接口的同时也提供原生的、性能更优的 pgx.Conn 接口。

初学者建议：从 lib/pq 或 pgx 的 database/sql 模式开始，因为它们用法完全一致，迁移方便。本课示例将使用 pgx，但其 SQL 操作方式与 lib/pq 完全通用。

连接与连接池

database/sql.Open 并不会立即建立物理连接，它只是初始化连接池的配置。实际连接会在首次执行查询时建立。连接池能有效复用连接，提升性能。

// 导入驱动后，使用 `sql.Open` 注册驱动并创建连接池
db, err := sql.Open("pgx", connString)

CRUD 操作

与 database/sql 基础课类似，核心方法为：

INSERT / UPDATE / DELETE: db.ExecContext()
SELECT (查询单行): db.QueryRowContext() 配合 .Scan()
SELECT (查询多行): db.QueryContext() 配合 rows.Next() 和 .Scan()

事务管理

事务是保证一组数据库操作要么全部成功，要么全部失败的关键机制。database/sql 提供了 db.BeginTx() 来开启事务，返回一个 *sql.Tx 对象。通过该对象执行操作，最后用 tx.Commit() 提交或 tx.Rollback() 回滚。

3. 代码示例

以下是一个完整的示例，演示了如何连接 PostgreSQL 并执行 CRUD 及事务操作。

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib" // 导入 pgx 的 database/sql 驱动
)

// User 结构体，映射数据库表
type User struct {
	ID        int
	Name      string
	Email     string
	CreatedAt time.Time
}

func main() {
	// 1. 构建连接字符串 (请替换为你的实际信息)
	// 格式：postgres://username:password@host:port/dbname?sslmode=disable
	connStr := "postgres://postgres:your_password@localhost:5432/testdb?sslmode=disable"

	// 2. 打开连接池
	db, err := sql.Open("pgx", connStr)
	if err != nil {
		log.Fatalf("无法连接数据库: %v", err)
	}
	defer db.Close() // 确保函数退出时关闭连接池

	// 3. 验证连接
	if err := db.PingContext(context.Background()); err != nil {
		log.Fatalf("无法 Ping 数据库: %v", err)
	}
	fmt.Println("✅ 成功连接到 PostgreSQL!")

	// 4. 创建表示例 (使用事务确保原子性)
	ctx := context.Background()
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		log.Fatalf("开启事务失败: %v", err)
	}

	// 创建表
	_, err = tx.ExecContext(ctx, `
		CREATE TABLE IF NOT EXISTS users (
			id SERIAL PRIMARY KEY,
			name VARCHAR(100) NOT NULL,
			email VARCHAR(255) UNIQUE NOT NULL,
			created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
		)
	`)
	if err != nil {
		tx.Rollback() // 出错立即回滚
		log.Fatalf("创建表失败: %v", err)
	}
	if err := tx.Commit(); err != nil {
		log.Fatalf("提交建表事务失败: %v", err)
	}
	fmt.Println("✅ 表 'users' 已就绪")

	// 5. INSERT: 插入一条记录
	var lastInsertId int
	insertSQL := `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id`
	err = db.QueryRowContext(ctx, insertSQL, "张三", "[email protected]").Scan(&lastInsertId)
	if err != nil {
		log.Fatalf("插入用户失败: %v", err)
	}
	fmt.Printf("✅ 插入成功，ID: %d\n", lastInsertId)

	// 6. SELECT (单行查询)
	var user User
	selectSQL := `SELECT id, name, email, created_at FROM users WHERE id = $1`
	err = db.QueryRowContext(ctx, selectSQL, lastInsertId).Scan(
		&user.ID, &user.Name, &user.Email, &user.CreatedAt,
	)
	if err != nil {
		log.Fatalf("查询用户失败: %v", err)
	}
	fmt.Printf("🔍 查询结果: %+v\n", user)

	// 7. UPDATE: 更新用户信息
	updateSQL := `UPDATE users SET name = $1 WHERE id = $2`
	result, err := db.ExecContext(ctx, updateSQL, "李四", lastInsertId)
	if err != nil {
		log.Fatalf("更新用户失败: %v", err)
	}
	rowsAffected, _ := result.RowsAffected()
	fmt.Printf("✏️  更新成功，影响行数: %d\n", rowsAffected)

	// 8. SELECT (多行查询)
	rows, err := db.QueryContext(ctx, `SELECT id, name, email FROM users`)
	if err != nil {
		log.Fatalf("查询所有用户失败: %v", err)
	}
	defer rows.Close() // 遍历完成后务必关闭 rows

	fmt.Println("\n📋 所有用户列表:")
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
			log.Printf("扫描行失败: %v", err)
			continue
		}
		fmt.Printf("  - ID:%d, Name:%s, Email:%s\n", u.ID, u.Name, u.Email)
	}
	if err := rows.Err(); err != nil {
		log.Fatalf("遍历行时出错: %v", err)
	}

	// 9. DELETE: 删除用户 (再次使用事务演示)
	tx2, _ := db.BeginTx(ctx, nil)
	deleteSQL := `DELETE FROM users WHERE id = $1`
	_, err = tx2.ExecContext(ctx, deleteSQL, lastInsertId)
	if err != nil {
		tx2.Rollback()
		log.Fatalf("删除用户失败: %v", err)
	}
	fmt.Printf("🗑️  已标记删除 ID: %d\n", lastInsertId)
	if err := tx2.Commit(); err != nil {
		log.Fatalf("提交删除事务失败: %v", err)
	}
}

运行前准备：

确保 PostgreSQL 服务已启动。
创建示例数据库：CREATE DATABASE testdb;
安装驱动：go get github.com/jackc/pgx/v5

4. 实践练习

练习 1：基础 CRUD 操作

基于上面的代码，创建一个 products 表，字段包括：id (自增主键), name (产品名), price (价格), stock (库存)。
编写代码，实现以下功能：
- 插入三条产品记录。
- 查询并打印所有价格大于 10 的产品。
- 将某个产品的库存增加 10。
- 删除库存为 0 的产品。 预期输出：程序运行无报错，并在控制台打印出正确的操作日志和查询结果。

练习 2：分页查询

编写一个函数 GetUsersByPage(db *sql.DB, page int, pageSize int) ([]User, error)，实现用户的分页查询。

要求使用 LIMIT 和 OFFSET。
总页数根据总记录数计算。预期：函数能正确返回指定页码的用户数据切片。

练习 3：事务与并发模拟

设计一个“转账”场景：

创建一个 accounts 表（id, balance）。
从账户 A 向账户 B 转账 100 元。
在事务中完成：检查 A 余额 >= 100，A 扣减 100，B 增加 100。
（进阶）使用 SELECT ... FOR UPDATE 对要更新的行加锁，模拟并发更新场景，防止余额出现负数。 预期输出：转账前后，两个账户的余额总和保持不变。

5. 常见错误

连接字符串格式错误：sslmode 参数必须正确设置。本地开发常用 sslmode=disable，生产环境应使用 sslmode=require 或更高级别。

忘记处理 Close 和错误：

rows, err := db.QueryContext(...)
// ❌ 错误：直接使用 rows，不检查 err
// ✅ 正确：先检查 err，然后 defer rows.Close()

在事务外使用事务对象的方法：

tx, _ := db.BeginTx(...)
// ❌ 错误：在事务开始前执行的操作，或使用 db 而非 tx
db.ExecContext(...) // 这不在事务中！
// ✅ 正确：所有属于该事务的操作都通过 tx 执行
tx.ExecContext(...)

忽略 Rows.Err()：遍历 rows.Next() 结束后，务必检查 rows.Err()，它可能包含在遍历过程中发生的错误。
事务未正确提交或回滚：在代码的每条可能出错的路径上，都必须确保调用 tx.Rollback()。使用 defer tx.Rollback() 是一个好习惯，即使最后提交成功，Rollback 也是无害的。

6. 小结

驱动选择：pgx 是当前推荐的高性能驱动，它兼容 database/sql 接口。
连接管理：使用 sql.Open 配置连接池，PingContext 验证连接。始终记得 defer db.Close()。
CRUD：熟练掌握 ExecContext、QueryRowContext 和 QueryContext 三种核心方法，并正确处理参数化查询（$1, $2）和结果扫描（Scan）。
事务至关重要：对于多个相关的写操作，务必使用 db.BeginTx 开启事务，通过 tx 对象执行操作，并在代码最后（或 defer 中）处理 Commit 和 Rollback，以保证数据一致性。
资源清理：rows 和 tx 等资源都需要妥善处理，defer 是你的好朋友。

掌握这些内容，你就可以在 Go 项目中稳健地操作 PostgreSQL 数据库了。下一课，我们将学习如何用类似的方法操作 MySQL。

PostgreSQL 操作