PostgreSQL 入门指南：从零开始掌握开源关系型数据库

如果你正在寻找一款稳定、强大且完全免费的关系型数据库，PostgreSQL 无疑是首选。它被誉为“世界上最先进的开源数据库”，不仅支持标准 SQL，还提供了丰富的数据类型、高级索引和扩展能力。本文将从零开始，带你快速上手 PostgreSQL，掌握安装、基本操作和核心概念。

安装与连接

PostgreSQL 支持主流操作系统。在 Ubuntu/Debian 上，只需运行：

sudo apt install postgresql postgresql-contrib

安装完成后，默认会创建一个名为 postgres 的系统用户和数据库超级用户。切换到该用户，进入命令行客户端：

sudo -i -u postgres
psql

在 psql 提示符下，你可以执行 SQL 命令。若使用 Windows 或 macOS，可以从官网下载安装包，图形化安装向导会引导你设置管理员密码和端口（默认 5432）。

连接数据库的基本语法是：

psql -h 主机 -p 端口 -U 用户名 -d 数据库名

例如连接本地数据库：

psql -U postgres -d postgres

数据库与表的基本操作

PostgreSQL 采用“数据库-模式-表”三级结构。一个实例可以管理多个数据库，每个数据库包含多个模式（默认 public），模式下才是表、视图等对象。

创建数据库使用：

CREATE DATABASE mydb;

然后连接到新数据库：

\c mydb;

创建表是入门的关键。以下语句创建一个用户表，包含常见字段：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• SERIAL 是自增整数，自动生成唯一 ID。
• PRIMARY KEY 指定主键，确保唯一性。
• UNIQUE 约束保证用户名不重复。
• DEFAULT 用于设置默认值，比如创建时间。

插入数据很简单：

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');

查询与过滤

查询是使用数据库最频繁的操作。基本的 SELECT 语句：

SELECT * FROM users;

按条件过滤：

SELECT username, email FROM users WHERE id = 1;

排序：

SELECT * FROM users ORDER BY created_at DESC;

PostgreSQL 支持丰富的条件运算符，如 LIKE 模糊匹配、IN 集合查询、BETWEEN 范围查询。例如查找用户名以 'a' 开头的用户：

SELECT * FROM users WHERE username LIKE 'a%';

表关联与 JOIN

关系型数据库的精髓在于表间关联。假设我们有一张订单表：

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product VARCHAR(100),
    amount NUMERIC(10,2),
    order_date DATE
);

REFERENCES 定义了外键，保证订单的 user_id 必须存在于 users 表。

若要查询每个订单的详细信息并附带用户名，使用内连接：

SELECT orders.id, users.username, orders.product, orders.amount
FROM orders
JOIN users ON orders.user_id = users.id;

左连接（LEFT JOIN）可以保留没有订单的用户，右侧字段为 NULL：

SELECT users.username, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

数据类型与高级特性

PostgreSQL 支持多种数据类型，远超标准 SQL。除了整数、浮点、字符、日期时间，还提供：

• 数组：INT[]、TEXT[]，例如存储标签。
• JSON/JSONB：支持 JSON 文档存储与查询。
• 几何类型：点、线、圆等。
• 网络地址：INET、CIDR。

例如使用 JSONB：

CREATE TABLE products (id SERIAL, info JSONB);
INSERT INTO products (info) VALUES ('{"name": "Laptop", "price": 999.99}');
SELECT info->>'name' FROM products;

此外，PostgreSQL 拥有强大的索引技术，如 B-tree、Hash、GiST、GIN 等，可以大幅提升查询性能。创建索引示例：

CREATE INDEX idx_users_username ON users(username);

事务与并发控制

事务保证一组操作要么全部成功，要么全部失败。PostgreSQL 完全支持 ACID 特性。使用事务的典型模式：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 或 ROLLBACK 撤销

PostgreSQL 采用多版本并发控制（MVCC），读操作不会阻塞写操作，适合高并发场景。

备份与恢复

生产环境务必定期备份。使用 pg_dump 工具导出数据库：

pg_dump -U postgres mydb > mydb_backup.sql

恢复时：

psql -U postgres mydb < mydb_backup.sql

对于大型数据库，可考虑并行备份或使用 pg_basebackup 进行物理备份。

延伸与生态

PostgreSQL 的扩展生态极为丰富。例如 PostGIS 提供地理空间能力，pg_trgm 支持模糊文本搜索，citus 实现水平分片。你可以通过 CREATE EXTENSION 加载它们。

结语

作为初学者，掌握基本的建表、增删改查、连接和事务已经足够应对多数应用场景。PostgreSQL 的官方文档非常详尽，社区活跃，遇到问题很容易找到解决方案。它的稳定性和性能使其成为从个人项目到大型企业的可靠选择。现在，你可以启动 psql，亲手尝试以上代码，感受开源数据库的魅力。祝你学习顺利！