PostgreSQL 入门指南

PostgreSQL 是一个功能强大的开源关系数据库管理系统（RDBMS, Relational Database Management System），以其稳定性、可扩展性和对 SQL 标准的高度支持而著称。本文将从安装配置开始，介绍 PostgreSQL 的核心概念和基本操作，帮助你快速上手这个企业级数据库。

Start

官方下载地址: www.postgresql.org/download/

使用 psql 命令行工具

# 基本连接方式
psql -U username -d database_name -h hostname
psql -U postgres # 连接本地数据库
psql -U postgres -p 5432 # 指定端口连接

# 使用连接字符串
psql postgresql://username:password@hostname:5432/database_name

核心概念

本章介绍关系数据库的核心概念及其在 PostgreSQL 中的实现。这些概念虽然在主流关系数据库中相似，但 PostgreSQL 在实现细节、命名规范和功能支持上有所差异。

参考文档: PostgreSQL SQL Commands - 官方完整 SQL 命令参考

数据库集群与数据库

数据库集群(Cluster)

数据库集群是 PostgreSQL 的顶层概念，指由一个 PostgreSQL 服务器实例管理的所有数据库的集合。

# 初始化一个新的数据库集群
initdb -D /path/to/data/directory

数据库(Database)

一个集群可以包含多个相互独立的数据库，每个数据库有自己的表、用户权限等。

-- 创建数据库
CREATE DATABASE myapp;

-- 查看所有数据库
\l

-- 切换到指定数据库
\c myapp

模式(Schema)

模式(Schema)是数据库内的命名空间，用于组织表、视图、函数等对象。每个数据库默认有一个 public 模式。

-- 创建模式
CREATE SCHEMA sales;

-- 在指定模式中创建表
CREATE TABLE sales.orders (
  id SERIAL PRIMARY KEY,
  amount DECIMAL(10, 2)
);

-- 查询指定模式的表
SELECT * FROM sales.orders;

-- 查看所有模式
\dn

表、视图与索引

表(Table)

表是存储数据的基本单位。

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

视图(View)

视图是基于查询的虚拟表，不存储数据。

-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE created_at > NOW() - INTERVAL '30 days';

-- 查询视图
SELECT * FROM active_users;

索引(Index)

索引用于加速查询，类似于书籍的目录。

-- 创建索引
CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

数据类型

PostgreSQL 支持丰富的数据类型：

类型分类	常用类型	示例	说明
数值类型	INTEGER, BIGINT, DECIMAL, NUMERIC	42, 3.14	整数和高精度小数
字符串类型	VARCHAR(n), TEXT, CHAR(n)	'hello'	变长、不限长、定长字符串
日期时间	DATE, TIME, TIMESTAMP, INTERVAL	'2025-12-10'	日期、时间、时间戳、时间间隔
布尔类型	BOOLEAN	TRUE, FALSE, NULL	布尔值
JSON	JSON, JSONB	'{"key": "value"}'	JSON 文本和二进制格式
数组	INTEGER[], TEXT[]	'{1,2,3}'	任意类型的数组
UUID	UUID	'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'	通用唯一标识符

JSONB 示例

-- 创建包含 JSONB 列的表
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  attributes JSONB
);

-- 插入 JSON 数据
INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Apple", "price": 1299, "specs": {"cpu": "M2", "ram": "16GB"}}');

-- 查询 JSON 字段
SELECT name, attributes->>'brand' AS brand
FROM products;

-- 查询嵌套字段
SELECT name, attributes->'specs'->>'cpu' AS cpu
FROM products;

-- 使用 JSON 条件查询
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple"}';

角色与用户

在 PostgreSQL 中，角色(Role)是权限管理的基本单位，统一了传统数据库中用户(User)和组(Group)的概念。

创建角色

-- 创建普通角色（不能登录，用作权限组）
CREATE ROLE readonly;

-- 创建可登录角色（推荐方式）
CREATE ROLE app_user WITH LOGIN PASSWORD 'securepassword';

-- 使用 CREATE USER（等价于上面的命令）
CREATE USER app_user PASSWORD 'securepassword';

-- 创建超级用户
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'adminpass';

角色属性：在 PostgreSQL17 中，Role 可以使用以下角色属性

选项	对立选项	默认值	说明
SUPERUSER	NOSUPERUSER	NOSUPERUSER	超级用户，绕过所有权限检查（除了登录权限）
CREATEDB	NOCREATEDB	NOCREATEDB	可以创建数据库
CREATEROLE	NOCREATEROLE	NOCREATEROLE	可以创建、修改、删除角色，授予/撤销成员资格
INHERIT	NOINHERIT	INHERIT	自动继承所属组角色的权限
LOGIN	NOLOGIN	NOLOGIN	可以作为初始会话用户名登录
REPLICATION	NOREPLICATION	NOREPLICATION	可以发起流复制和激活/停用备份模式
BYPASSRLS	NOBYPASSRLS	NOBYPASSRLS	绕过行级安全策略（Row Level Security）
CONNECTION LIMIT	-	-1（无限制）	可以建立的最大并发连接数，-1 表示无限制
PASSWORD	-	NULL	设置密码（仅用于有 LOGIN 的角色）
VALID UNTIL	-	NULL	密码过期时间戳，NULL 表示永不过期
IN ROLE	-	-	新角色立即成为这些角色的成员
ROLE	-	-	这些角色立即成为新角色的成员
ADMIN	-	-	类似 ROLE，但新角色可以授予/撤销这些角色的成员资格

-- 赋予角色更多属性
CREATE ROLE developer WITH
  LOGIN
  PASSWORD 'devpass'
  CREATEDB           -- 可以创建数据库
  CREATEROLE         -- 可以创建角色
  VALID UNTIL '2026-01-01';  -- 密码过期时间

-- 修改角色属性
ALTER ROLE developer WITH NOCREATEDB;

角色继承

-- 创建组角色
CREATE ROLE sales_team;

-- 将用户添加到组
GRANT sales_team TO alice, bob;

-- alice 和 bob 现在继承 sales_team 的所有权限

权限管理

PostgreSQL 使用细粒度的权限控制系统。在 PostgreSQL17 中，有以下权限

对象类型	对象说明	可用权限	支持批量授权
TABLE	数据表，存储结构化数据的基本单位	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, MAINTAIN	✓
TABLE.column	数据表中的每一列	SELECT, INSERT, UPDATE, REFERENCES	✓
SEQUENCE	序列，用于生成唯一数字（常用于自增主键）	USAGE, SELECT, UPDATE	✓
DATABASE	数据库，包含 Schema、表等对象的顶层容器	CREATE, CONNECT, TEMPORARY/TEMP	✗
SCHEMA	模式，数据库内的命名空间，组织表和对象	CREATE, USAGE	✗
DOMAIN	域，自定义数据类型（带约束的基础类型）	USAGE	✗
FOREIGN DATA WRAPPER	外部数据包装器，用于访问外部数据源的接口	USAGE	✗
FOREIGN SERVER	外部服务器，FDW 的具体连接配置	USAGE	✗
FUNCTION	函数，返回值的可执行代码块	EXECUTE	✓
PROCEDURE	存储过程，不返回值的可执行代码块	EXECUTE	✓
ROUTINE	例程，函数和过程的统称	EXECUTE	✓
LANGUAGE	编程语言，用于编写函数/过程的语言环境	USAGE	✗
LARGE OBJECT	大对象，存储超大二进制数据（如图片、视频）	SELECT, UPDATE	✗
PARAMETER	配置参数，数据库或会话的配置选项	SET, ALTER SYSTEM	✗
TABLESPACE	表空间，定义数据库对象的物理存储位置	CREATE	✗
TYPE	自定义类型，用户定义的复合类型或枚举类型	USAGE	✗

对象级权限

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE myapp TO app_user;

-- 授予模式使用权限
GRANT USAGE ON SCHEMA public TO app_user;

-- 授予表的查询权限
GRANT SELECT ON TABLE users TO readonly;

-- 授予表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO app_user;

-- 授予所有权限
GRANT ALL PRIVILEGES ON TABLE users TO admin;

-- 授予序列使用权限（用于 SERIAL 列）
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO app_user;

批量授权

-- 授予模式内所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 为未来创建的表设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

撤销权限

-- 撤销权限
REVOKE INSERT, UPDATE, DELETE ON TABLE users FROM app_user;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON TABLE users FROM app_user;

查看权限

-- 查看表的权限
\dp users

-- 或使用 SQL 查询
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'users';

实战案例

本章通过一个博客系统的实战案例，展示如何使用 PostgreSQL 设计数据库、创建表结构并执行常见操作。

案例背景

我们要构建一个简单的博客系统，需要实现以下功能：

• 用户注册和管理
• 文章发布和分类
• 文章标签
• 用户评论

准备工作

第 1 步：创建数据库

-- 创建博客数据库
CREATE DATABASE blog_system;

第 2 步：连接到数据库

-- 使用 psql 连接
\c blog_system

-- 或者从命令行连接
psql -U postgres -d blog_system

数据库设计

ER 关系说明

• 一个用户可以发布多篇文章（一对多）
• 一个分类可以包含多篇文章（一对多）
• 一篇文章可以有多个标签（多对多）
• 一篇文章可以有多条评论（一对多）

表结构设计

-- 1. 用户表
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 分类表
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL,
  description TEXT
);

-- 3. 文章表
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  user_id INTEGER NOT NULL,
  category_id INTEGER,
  view_count INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP,
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_category
    FOREIGN KEY (category_id)
    REFERENCES categories(id)
    ON DELETE SET NULL
);

-- 4. 标签表
CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

-- 5. 文章标签关联表（多对多）
CREATE TABLE post_tags (
  post_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  CONSTRAINT fk_post
    FOREIGN KEY (post_id)
    REFERENCES posts(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_tag
    FOREIGN KEY (tag_id)
    REFERENCES tags(id)
    ON DELETE CASCADE
);

-- 6. 评论表
CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_comment_post
    FOREIGN KEY (post_id)
    REFERENCES posts(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_comment_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
);

-- 验证表是否创建成功
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

执行后应该看到 6 个表：categories, comments, post_tags, posts, tags, users

创建索引

为常用查询字段创建索引以提升性能：

-- 文章表索引
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_category_id ON posts(category_id);
CREATE INDEX idx_posts_created_at ON posts(created_at);

-- 评论表索引
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);

插入测试数据

-- 插入用户
INSERT INTO users (id, username, email, password_hash)
VALUES
  (1, 'alice', 'alice@blog.com', 'hash1'),
  (2, 'bob', 'bob@blog.com', 'hash2'),
  (3, 'charlie', 'charlie@blog.com', 'hash3');

-- 插入分类
INSERT INTO categories (id, name, description)
VALUES
  (1, 'Technology', 'Tech articles'),
  (2, 'Travel', 'Travel experiences'),
  (3, 'Food', 'Food and recipes');

-- 插入文章
INSERT INTO posts (id, title, content, user_id, category_id, view_count)
VALUES
  (1, 'PostgreSQL Guide', 'This is a comprehensive guide...', 1, 1, 100),
  (2, 'Trip to Paris', 'My amazing trip to Paris...', 1, 2, 50),
  (3, 'Best Pizza Recipe', 'How to make perfect pizza...', 2, 3, 200);

-- 插入标签
INSERT INTO tags (id, name)
VALUES
  (1, 'database'),
  (2, 'postgresql'),
  (3, 'travel'),
  (4, 'europe'),
  (5, 'cooking');

-- 插入文章标签关联
INSERT INTO post_tags (post_id, tag_id)
VALUES
  (1, 1), -- PostgreSQL Guide - database
  (1, 2), -- PostgreSQL Guide - postgresql
  (2, 3), -- Trip to Paris - travel
  (2, 4), -- Trip to Paris - europe
  (3, 5); -- Best Pizza Recipe - cooking

-- 插入评论
INSERT INTO comments (id, post_id, user_id, content)
VALUES
  (1, 1, 2, 'Great guide! Very helpful.'),
  (2, 1, 3, 'Thanks for sharing!'),
  (3, 2, 3, 'Paris is amazing!'),
  (4, 3, 1, 'I tried this recipe, it works!');

常用查询示例

查询用户发布的所有文章及分类

SELECT u.username,
       p.title,
       c.name AS category,
       p.view_count,
       p.created_at
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE u.username = 'alice'
ORDER BY p.created_at DESC;

查询文章及其所有标签

SELECT p.title,
       STRING_AGG(t.name, ', ') AS tags
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.title;

查询最热门的文章（按浏览量和评论数）

SELECT p.title,
       p.view_count,
       COUNT(c.id) AS comment_count,
       u.username AS author
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id, p.title, p.view_count, u.username
ORDER BY p.view_count DESC, comment_count DESC
LIMIT 10;

查询用户的文章统计

SELECT u.username,
       COUNT(p.id) AS post_count,
       SUM(p.view_count) AS total_views,
       AVG(p.view_count) AS avg_views
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
HAVING COUNT(p.id) > 0
ORDER BY total_views DESC;

查询包含特定标签的文章

SELECT p.title,
       u.username AS author,
       p.view_count
FROM posts p
INNER JOIN users u ON p.user_id = u.id
INNER JOIN post_tags pt ON p.id = pt.post_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE t.name IN ('database', 'postgresql')
ORDER BY p.created_at DESC;

使用 CTE 查询活跃用户（发布文章和评论最多的用户）

WITH user_posts AS (
  SELECT user_id, COUNT(*) AS post_count
  FROM posts
  GROUP BY user_id
),
user_comments AS (
  SELECT user_id, COUNT(*) AS comment_count
  FROM comments
  GROUP BY user_id
)
SELECT u.username,
       COALESCE(up.post_count, 0) AS posts,
       COALESCE(uc.comment_count, 0) AS comments,
       COALESCE(up.post_count, 0) + COALESCE(uc.comment_count, 0) AS total_activity
FROM users u
LEFT JOIN user_posts up ON u.id = up.user_id
LEFT JOIN user_comments uc ON u.id = uc.user_id
ORDER BY total_activity DESC;

数据管理操作

在实际应用中，我们经常需要更新和删除数据。

更新数据

-- 更新文章浏览量
UPDATE posts
SET view_count = view_count + 1
WHERE id = 1;

-- 更新文章内容和修改时间
UPDATE posts
SET content = 'Updated content...',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- 批量更新：修改所有文章的浏览量
UPDATE posts
SET view_count = view_count + 10
WHERE category_id = 1;

删除数据

-- 删除指定评论
DELETE FROM comments WHERE id = 1;

-- 删除某用户的所有评论
DELETE FROM comments WHERE user_id = 2;

-- 删除浏览量低于 10 的文章（会级联删除相关评论和标签关联）
DELETE FROM posts WHERE view_count < 10;

表结构调整

随着业务发展，可能需要调整表结构。

-- 为 users 表添加新字段
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(255);

-- 为 posts 表添加发布状态字段
ALTER TABLE posts ADD COLUMN is_published BOOLEAN DEFAULT FALSE;

-- 为 categories 表添加排序字段
ALTER TABLE categories ADD COLUMN sort_order INTEGER DEFAULT 0;

-- 示例：删除刚添加的字段
ALTER TABLE users DROP COLUMN bio;

用户和权限管理

在生产环境中，需要为不同角色创建用户并分配权限。

-- 创建只读用户
CREATE USER blog_reader WITH PASSWORD 'reader123';
GRANT CONNECT ON DATABASE blog_system TO blog_reader;
GRANT USAGE ON SCHEMA public TO blog_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO blog_reader;

-- 创建编辑用户（可以增删改查）
CREATE USER blog_editor WITH PASSWORD 'editor123';
GRANT CONNECT ON DATABASE blog_system TO blog_editor;
GRANT USAGE ON SCHEMA public TO blog_editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO blog_editor;

-- 查看用户权限
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee IN ('blog_reader', 'blog_editor')
ORDER BY grantee, table_name;