PostgreSQL 增删改和内置语句¶

查看数据库

\l

SELECT datname FROM pg_databases;

接入数据库

\c db_name;

第一次登录库先用postgres用户身份登录 sudo -u postgres psql

参数

--name postgres 18

db=# CREATE TABLE IF NOT EXISTS files(
id BIGINT GENERATED BY DEFAULT AS IDENTIFY 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[],                                                        -- 数组类型(pgsql特色)
created_at TIMESTAMPIZ DEFAULT NOW()                                -- 带时区的时间
);

CREATE TABLE IF NOT EXISTS files( id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, file_id UUID DEFAULT gen_random_uuid(), file_name TEXT NOT NULL, file_size BIGINT CHECK (file_size >= 0), is_public BOOLEAN DEFAULT false, tags TEXT[], created_at TIMESTAMPTZ DEFAULT NOW() );

增加字段 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_pubilc TO is_shared; 删除字段 ALTER TABLE storage.files DROP COLUMN tags;

删除表 DORP TABLE IF EXISTS storage.files;

清空表 TRUNCATE TABLE storage.files; TRUNCATE TABLE storage.files RESTART IDENTITY; //清空表并重置自增ID

\dt 列出所有表

\d表名查看表结构

\d storage.files查看表的详细结构(列，类型，索引，约束)

表继承你可以创建一个父表，让子表继承他 CREATE TABLE content ( TITLE text, author text );

-- 子表继承基础字段并增加自己字段 CREATE TABLE video ( DURATION int )INHERITS (content);

查询 SELECT colum_name,data_type,is_nullable FROM information_scheam.columns WHERE table_name= 'files';

数据操作

对数据增删改查

插入 -- 插入单条记录(注意数组用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',500000,'{"视频","测试"}'), ('config.yaml',1024,'{"配置"}');

多个条件查询

SELECT * FROM files WHERE is_pubilc = faluse and file_size >= 1024;
SELECT * FROM files WHERE file_name like "%.mp4";
SELECT * FROM files WHERE file_name like "dem_.mp4";
SELECT * FROM files WHERE file_name like "张__";
--数组查询，PG特色
SELECT * FROM files WHERE '视频' = any(tags);

修改

UPDATE file SET file_name = '课程大纲.pdf' WHERE ID = 1;

创建加回显 -- 插入并立即返回新的生存的UUID和创建时间

INSERT INTO files (file_name,file_size) VALUES ('峰峰老师的私教课.zip',99999) RETURNING id,file_id,created_at;

约束类型描述示例

NOT NULL 强制列不能包含NULL值 name TEXT NOT NULL UNIQUE 确保列中所有值互不相同 email TEXT UNIQUE PRIMARY 主键，唯一标识符号，隐含not null和UNIQUE id SERIAL PRIMARY KEY FOREIGN KEY 外键，建立表与表的连接，防止破坏关系 user_id INT REFERENCES users(id) CHECK 检查值是否满足特定逻辑条件 age INT CHECK (age >=18) DEFAULT 如果插入时未指定值，则使用默认值 create_at TIMESTAMPTZ DEAFULT NOW()

JSON JSONB查询 ""

时间

查询.png

SELECT * FROM file_details WHERE file_name like "%.png"; 忽略大小写 SELECT * FROM file_details WHERE file_name ilike "%.png";

SELECT file_name,created_at from file_details WHERE created_at >= '2026-01-01' and created_at <= '2027-01-01';