pgsql基本结构

理解 PostgreSQL 的结构，可以把它理解成一栋行政办公大楼。在 PostgreSQL 中，数据的组织是层层嵌套的，每一层都有其特定的边界和作用。核心层级结构 PostgreSQL 的

理解 PostgreSQL 的结构，可以把它理解成一栋行政办公大楼。

在 PostgreSQL 中，数据的组织是层层嵌套的，每一层都有其特定的边界和作用。

核心层级结构

PostgreSQL 的逻辑结构从大到小依次为：实例 (Instance) > 数据库 (Database) > 模式 (Schema) > 表 (Table) > 行与列 (Row & Column)

第一层：数据库 (Database)

类比： 大楼里的某一层楼。
特点： 数据库是物理上的最高隔离级别。通常一个项目会占用一个独立的数据库。
**注意：**不同数据库之间的数据默认是不通的，你不能在db_a里直接查询db_b 的表。

第二层：模式 (Schema) —— 这是 PG 的灵魂

类比： 楼层里的各个“部门”或“房间”。
特点： 这是 PostgreSQL 区别于 MySQL 的重要特性。一个数据库下可以有多个 Schema。
**默认值：**默认所有的表都放在名为public 的模式下。
**用途：**你可以创建auth模式放用户表，storage 模式放文件表，从而实现逻辑上的分组和权限控制。

第三层：表 (Table)

类比： 房间里的“文件柜”。
特点： 这是真正存放数据的地方。
结构： 每一行（Row）代表一条记录，每一列（Column）代表一个字段。

为什么这么设计？

这种“数据库 -> 模式 -> 表”的设计方式，能让你在开发大型复杂系统时非常从容。比如，如果你要做多租户系统（每个客户的数据逻辑隔离），你可以给每个客户分配一个独立的Schema，但它们共享同一个数据库连接池，这样既安全又省资源。

创建库、模式

数据库操作

# 创建数据库
create database db_name;
// 查看数据库
\l 或者 SELECT datname FROM pg_database;
// 接入数据库
\c db_name;
// 删除数据库
drop database db_name; // 需要先切换到其他数据库才能删除，并且不能有会话连接
DROP DATABASE db WITH (FORCE); // 强制删除

模式操作

如果是小项目，直接用public模式就行

// 如果不存在，就创建
CREATE SCHEMA IF NOT EXISTS storage;
// 查看模式
\dn
// 删除模式
-- 只有模式为空时才能删除
DROP SCHEMA IF EXISTS storage;

-- 【慎用】级联删除：删除模式以及模式里的所有表、视图、函数等
DROP SCHEMA IF EXISTS storage CASCADE;

pgsql表操作

1. 创建表 (CREATE TABLE)

在建表时，除了字段名和类型，约束（Constraints） 是保证数据质量的关键。

// storage.files  这样写表示在storage下创建files表
// 如果要在public模式下创建files表，直接写files或者public.files
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,                    -- 默认值
    tags TEXT[],                                        -- 数组类型（PG特色！）
    created_at TIMESTAMPTZ DEFAULT NOW()                -- 带时区的时间
);

查这个库下的表列表 \d

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

查这个表的表结构 \d 表名或者 \d+ 表名

SELECT
    column_name AS 字段名,
    data_type AS 数据类型,
    is_nullable AS 是否允许为空,
    column_default AS 默认值,
    character_maximum_length AS 最大长度
FROM information_schema.columns
WHERE table_name = 'cloud_files'  -- 替换成你的表名
ORDER BY ordinal_position;      -- 按照表中定义的顺序排列

2. 修改表 (ALTER TABLE)

项目需求经常变，你需要修改已经存在的表结构。

添加字段：

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;

3. 删除表 (DROP TABLE)

删除表要非常谨慎，一旦删除，表内数据将全部丢失。

-- 基本删除
DROP TABLE IF EXISTS storage.files;

-- 级联删除（如果这个表被其他表作为外键关联，用CASCADE会连带删除关联）
DROP TABLE storage.files CASCADE;

4. 清空表 (TRUNCATE)

如果你只想删掉表里所有的数据，但想保留这个表的结构（就像把房子里的家具搬空，但房子不拆），TRUNCATE比DELETE 快得多。

TRUNCATE TABLE storage.files;
TRUNCATE TABLE storage.files i; // 清空表并且重置自增id

5. 查看表信息 (元指令与查询)

在操作表时，你经常需要确认当前的结构。

在 psql 命令行中：
- \dt: 列出当前库所有表。
- \d storage.files: 查看这张表的详细结构（列、类型、索引、约束）。
使用 SQL 查询（pgAdmin常用）：

SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_name = 'files';

6. 特色操作：表继承 (Inheritance)

这是 PostgreSQL 的黑科技之一。你可以创建一个“父表”，然后让“子表”继承它。

-- 父表：定义基础字段
CREATE TABLE content (
    title TEXT,
    author TEXT
);

-- 子表：继承基础字段，并增加自己的字段
CREATE TABLE video (
    duration INT
) INHERITS (content);

查询content表时，你会发现它会自动把video 表里的数据也查出来。

数据操作

对数据的增删改查

插入

-- 插入单条记录（注意数组用 ARRAY[...] 或 {'a','b'} 语法）
INSERT INTO files (file_name, file_size, is_public, tags)
VALUES ('课程大纲.pdf', 1024576, true, ARRAY['教育', 'PDF']);

-- 批量插入（Postgres 对批量插入的支持非常高效）
INSERT INTO files (file_name, file_size, tags)
VALUES 
    ('demo.mp4', 50000000, '{"视频", "测试"}'),
    ('config.yaml', 1024, '{"配置"}');

如果约束失败，自增id也是会消耗的

查询

-- 基础查询：查看所有公开的大文件
SELECT file_name, file_size, created_at 
FROM files 
WHERE is_public = true AND file_size > 1000000;

-- 模糊查询：查找所有 pdf
SELECT * FROM files WHERE file_name LIKE '%.pdf';

-- 数组查询（PG特色）：查找标签中包含“视频”的文件
SELECT * FROM files WHERE '视频' = ANY(tags);

修改

-- 模拟下载次数自增
UPDATE files 
SET download_count = download_count + 1 
WHERE file_name = 'demo.mp4';

-- 修改多个字段并切换布尔值
UPDATE files 
SET is_public = false, tags = array_append(tags, '私有')
WHERE file_id = '你查出来的某个UUID';

删除

-- 删除特定的文件
DELETE FROM files WHERE file_id = '某个UUID';

-- 删除所有下载次数为 0 的旧配置测试文件
DELETE FROM files 
WHERE download_count = 0 AND tags @> ARRAY['测试'::text];

创建+回显

-- 插入并立刻返回新生成的 UUID 和创建时间
INSERT INTO files (file_name, file_size)
VALUES ('枫枫老师的私房课.zip', 999999)
RETURNING file_id, created_at;