PostgreSQL 基础知识整理

对于熟悉 MySQL 的开发者而言，PostgreSQL 最核心的差异在于其多层级的逻辑架构体系。掌握该分层结构是系统学习 PostgreSQL 的基础

一、PostgreSQL 逻辑层级架构

PostgreSQL 的数据组织采用自上而下的嵌套层级结构，从高到低依次为：实例(Instance) → 数据库(Database) → 模式(Schema) → 数据表(Table) → 行与列

1. 实例 Instance

实例是 PostgreSQL 数据库服务的运行本体，由一组常驻后台进程与共享内存空间构成。一台物理服务器可运行多个独立实例，每个实例监听独立端口。数据库连接的目标即为实例，实例管辖其下所有数据库与数据资源

2. 数据库 Database

数据库是 PostgreSQL 最高层级的物理隔离单元。同一实例下的不同数据库之间相互独立，默认状态下无法直接跨库查询。若需实现跨库数据访问，需借助 dblink 或 FDW 扩展插件。工程实践中，通常为单个独立业务项目分配一个专属数据库，实现项目间的数据物理隔离

3. 模式 Schema

模式是 PostgreSQL 的核心特性，用于数据库内部的逻辑隔离与对象分组。单个数据库内可创建多个独立模式，实现数据表、视图、函数等对象的按域归类，同时支持精细化的权限管控

默认行为：未指定模式时，新建对象默认归属 public 模式
企业级用法：按业务域拆分模式，如 auth 存储权限相关表、storage 存储资源相关表、log 存储日志相关表。该架构天然适配多租户场景：多租户共享同一数据库实例与连接池，通过独立模式实现数据逻辑隔离，降低资源开销

4. 数据表 Table

数据表是业务数据的实际存储载体。表中每行对应一条业务记录，每列对应一个数据字段。 PostgreSQL 属于强类型关系型数据库，每一列都定义了严格的数据类型与约束规则，从写入层面保障数据合法性与一致性

二、数据库与模式基础操作

以下操作均基于标准 PostgreSQL 语法，兼容主流发行版本

数据库常用操作

-- 创建数据库
CREATE DATABASE db_name;

-- psql 元命令：查看实例下所有数据库
\l

-- SQL 方式查询数据库列表
SELECT datname FROM pg_database;

-- 切换连接至指定数据库
\c db_name;

-- 删除数据库（需无活跃会话，执行前需切换至其他数据库）
DROP DATABASE db_name;

-- 强制删除数据库（强制断开所有活跃连接后删除，高危操作慎用）
DROP DATABASE db_name WITH (FORCE);

模式 Schema 常用操作

小型项目可直接使用默认 public 模式，中大型项目建议按业务域拆分自定义模式

-- 不存在则创建模式
CREATE SCHEMA IF NOT EXISTS storage;

-- psql 元命令：查看当前数据库所有模式
\dn

-- 删除空模式
DROP SCHEMA IF EXISTS storage;

-- 级联删除（删除模式及所属所有表、视图、函数等对象，高危操作慎用）
DROP SCHEMA IF EXISTS storage CASCADE;

三、数据表核心操作

PostgreSQL 支持丰富的数据类型与约束定义，可在建表阶段完成数据校验规则配置

1. 创建数据表

示例：在 storage 模式下创建文件资源表，覆盖常用类型与约束：

CREATE TABLE IF NOT EXISTS storage.files (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 自增主键
    file_id UUID DEFAULT gen_random_uuid(),             -- 系统自动生成唯一UUID
    file_name TEXT NOT NULL,                            -- 文件名称，非空约束
    file_size BIGINT CHECK (file_size >= 0),            -- 检查约束：文件大小非负
    is_public BOOLEAN DEFAULT false,                    -- 是否公开，默认值为false
    tags TEXT[],                                        -- 字符串数组类型
    created_at TIMESTAMPTZ DEFAULT NOW()                -- 带时区的时间戳
);

2. 查询表信息

查询当前库所有业务表（过滤系统内置表）：

SELECT 
    schemaname AS 模式名, 
    tablename AS 表名, 
    tableowner AS 表拥有者 
FROM pg_tables 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

查询单表字段结构详情：

SELECT
    column_name AS 字段名,
    data_type AS 数据类型,
    is_nullable AS 是否允许为空,
    column_default AS 默认值,
    character_maximum_length AS 字段最大长度
FROM information_schema.columns
WHERE table_name = 'files'
ORDER BY ordinal_position;

psql 终端快捷元命令：

\dt：列出当前库所有数据表
\d 模式名.表名：查看单表完整结构、索引与约束信息

3. 修改数据表结构

-- 新增字段
ALTER TABLE storage.files ADD COLUMN download_count INT DEFAULT 0;

-- 修改字段数据类型
ALTER TABLE storage.files ALTER COLUMN file_name TYPE VARCHAR(255);

-- 重命名字段
ALTER TABLE storage.files RENAME COLUMN is_public TO is_shared;

-- 删除指定字段
ALTER TABLE storage.files DROP COLUMN tags;

4. 删除与清空表

-- 安全删除表，表不存在则不报错
DROP TABLE IF EXISTS storage.files;

-- 级联删除，同步删除关联外键的相关对象，慎用
DROP TABLE storage.files CASCADE;

-- 清空表全部数据，保留表结构，执行效率远高于 DELETE
TRUNCATE TABLE storage.files;

-- 清空数据并重置自增ID序列
TRUNCATE TABLE storage.files RESTART IDENTITY;

四、表继承特性

表继承是 PostgreSQL 的高级特性，支持定义基础父表，子表继承父表全部字段定义，并可扩展自身专属字段

-- 定义父表，包含通用基础字段
CREATE TABLE content (
    title TEXT,
    author TEXT
);

-- 定义子表，继承父表字段并新增专属字段
CREATE TABLE video (
    duration INT
) INHERITS (content);

特性：对父表 content 执行查询时，结果会自动包含所有继承该父表的子表数据，适用于业务数据分层、分类存储场景

五、基础 CRUD 数据操作

1. 插入数据

-- 单条数据插入
INSERT INTO storage.files (file_name, file_size, is_public, tags)
VALUES ('课程大纲.pdf', 1024576, true, ARRAY['教育', 'PDF']);

-- 批量数据插入
INSERT INTO storage.files (file_name, file_size, tags)
VALUES 
    ('demo.mp4', 50000000, '{"视频", "测试"}'),
    ('config.yaml', 1024, '{"配置"}');

注意：插入因约束校验失败回滚后，自增主键ID仍会被消耗，不会复用

2. 查询数据

-- 条件筛选查询
SELECT file_name, file_size, created_at 
FROM storage.files 
WHERE is_public = true AND file_size > 1000000;

-- 模糊匹配查询
SELECT * FROM storage.files WHERE file_name LIKE '%.pdf';

-- 数组包含查询：匹配标签中包含指定值的记录
SELECT * FROM storage.files WHERE '视频' = ANY(tags);

3. 更新数据

-- 字段数值自增
UPDATE storage.files 
SET download_count = download_count + 1 
WHERE file_name = 'demo.mp4';

-- 多字段更新 + 数组元素追加
UPDATE storage.files 
SET is_public = false, tags = array_append(tags, '私有')
WHERE file_id = '指定UUID';

4. 删除数据

-- 根据主键精准删除
DELETE FROM storage.files WHERE file_id = '指定UUID';

-- 按条件批量删除
DELETE FROM storage.files 
WHERE download_count = 0 AND tags @> ARRAY['测试'::text];

5. 插入并回显

通过 RETURNING 子句可在插入完成后直接返回指定字段，无需二次查询：

INSERT INTO storage.files (file_name, file_size)
VALUES ('私有资源包.zip', 999999)
RETURNING file_id, created_at;

六、核心要点总结

PostgreSQL 核心分层为 实例 > 数据库（物理隔离） > 模式（逻辑隔离） > 表 > 行列数据，其中 Schema 是其与 MySQL 架构的核心差异
开发规范：小型项目可直接使用默认 public 模式；中大型项目建议按业务域拆分自定义模式，适配权限管控、多租户等场景
PostgreSQL 原生支持数组类型、表继承、RETURNING 返回子句等特色功能，合理使用可简化业务SQL实现
全量清空表数据优先使用 TRUNCATE，执行性能显著优于 DELETE，且不破坏表结构定义

Linzex

PostgreSQL 基础知识整理

一、PostgreSQL 逻辑层级架构

1. 实例 Instance

2. 数据库 Database

3. 模式 Schema

4. 数据表 Table

二、数据库与模式基础操作

数据库常用操作

模式 Schema 常用操作

三、数据表核心操作

1. 创建数据表

2. 查询表信息

3. 修改数据表结构

4. 删除与清空表

四、表继承特性

五、基础 CRUD 数据操作

1. 插入数据

2. 查询数据

3. 更新数据

4. 删除数据

5. 插入并回显

六、核心要点总结

PostgreSQL 基础知识整理

评论交流