PostgreSQL DDL 数据定义

数据库操作#

单行写法：创建数据库 CREATE DATABASE <库名>;

-- 创建数据库
CREATE DATABASE mydb;

换行写法：指定所有者与编码 CREATE DATABASE <库名> [OWNER <所有者>] [ENCODING '<编码>'] [LC_COLLATE '<排序>'] [LC_CTYPE '<类型>'] [TEMPLATE <模板>];

-- 创建指定编码与所有者的数据库
CREATE DATABASE mydb
  OWNER appuser
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0;

单行写法：删除数据库 DROP DATABASE [IF EXISTS] <库名>;

-- 存在时才删除
DROP DATABASE IF EXISTS mydb;

单行写法：切换数据库 \c <库名>

-- 在 psql 中切换数据库
\c mydb

Schema 模式#

单行写法：创建模式 CREATE SCHEMA [IF NOT EXISTS] <模式名> [AUTHORIZATION <用户>];

-- 创建模式并指定所有者
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION appuser;

单行写法：删除模式 DROP SCHEMA [IF EXISTS] <模式名> [CASCADE];

-- 级联删除模式及其所有对象
DROP SCHEMA IF EXISTS app_schema CASCADE;

单行写法：设置搜索路径 SET search_path TO <模式1>, <模式2>, public;

-- 设置模式搜索路径
SET search_path TO app_schema, public;

单行写法：查看当前搜索路径 SHOW search_path;

-- 查看当前搜索路径
SHOW search_path;

创建表#

换行写法：创建表（SERIAL 自增） CREATE TABLE [IF NOT EXISTS] <表名> (<列定义>[, <表约束>...]);

-- 创建用户表使用 SERIAL 自增
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  age INT CHECK (age >= 0 AND age < 150),
  balance NUMERIC(10,2) DEFAULT 0.00,
  status SMALLINT DEFAULT 1,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

换行写法：使用 IDENTITY 列（PG10+） <列名> INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- 使用标准 IDENTITY 列替代 SERIAL
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username TEXT NOT NULL
);

换行写法：创建带外键的表 CREATE TABLE <表名> (<列定义>, FOREIGN KEY (<列>) REFERENCES <父表>(<列>) [ON DELETE <动作>]);

-- 创建订单表带外键
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  order_no VARCHAR(32) UNIQUE NOT NULL,
  user_id INT NOT NULL,
  total_amount NUMERIC(10,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

单行写法：创建临时表 CREATE TEMP TABLE <表名> AS SELECT * FROM <源表> WHERE <条件>;

-- 创建会话级临时表
CREATE TEMP TABLE temp_users AS SELECT * FROM users WHERE status = 1;

单行写法：复制表结构 CREATE TABLE <新表> (LIKE <源表> [INCLUDING DEFAULTS] [INCLUDING CONSTRAINTS]);

-- 完整复制表结构包含约束
CREATE TABLE users_copy (LIKE users INCLUDING ALL);

修改表#

单行写法：添加列 ALTER TABLE <表名> ADD COLUMN [IF NOT EXISTS] <列名> <类型> [<约束>];

-- 添加新列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

单行写法：删除列 ALTER TABLE <表名> DROP COLUMN [IF EXISTS] <列名> [CASCADE];

-- 级联删除列及其依赖对象
ALTER TABLE users DROP COLUMN IF EXISTS phone CASCADE;

单行写法：修改列类型 ALTER TABLE <表名> ALTER COLUMN <列名> TYPE <新类型> [USING <转换表达式>];

-- 修改列类型并指定转换
ALTER TABLE users ALTER COLUMN phone TYPE BIGINT USING phone::BIGINT;

单行写法：设置列默认值 ALTER TABLE <表名> ALTER COLUMN <列名> SET DEFAULT <默认值>;

-- 设置列默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 1;

单行写法：删除列默认值 ALTER TABLE <表名> ALTER COLUMN <列名> DROP DEFAULT;

-- 删除列默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

单行写法：设置非空 ALTER TABLE <表名> ALTER COLUMN <列名> SET NOT NULL;

-- 设置列为非空
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

单行写法：删除非空约束 ALTER TABLE <表名> ALTER COLUMN <列名> DROP NOT NULL;

-- 取消非空约束
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

单行写法：重命名列 ALTER TABLE <表名> RENAME COLUMN <旧名> TO <新名>;

-- 重命名列
ALTER TABLE users RENAME COLUMN phone TO telephone;

单行写法：重命名表 ALTER TABLE <旧表名> RENAME TO <新表名>;

-- 重命名表
ALTER TABLE users RENAME TO user_info;

约束管理#

单行写法：添加主键 ALTER TABLE <表名> ADD CONSTRAINT <约束名> PRIMARY KEY (<列>);

-- 添加主键约束
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

单行写法：添加唯一约束 ALTER TABLE <表名> ADD CONSTRAINT <约束名> UNIQUE (<列>);

-- 添加唯一约束
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

单行写法：添加 CHECK 约束 ALTER TABLE <表名> ADD CONSTRAINT <约束名> CHECK (<条件>);

-- 添加检查约束
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

单行写法：添加外键 ALTER TABLE <表名> ADD CONSTRAINT <约束名> FOREIGN KEY (<列>) REFERENCES <父表>(<列>) ON DELETE <动作>;

-- 添加外键约束
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

单行写法：删除约束 ALTER TABLE <表名> DROP CONSTRAINT [IF EXISTS] <约束名> [CASCADE];

-- 删除约束
ALTER TABLE users DROP CONSTRAINT IF EXISTS uk_email;

删除与清空#

单行写法：删除表 DROP TABLE [IF EXISTS] <表名>[, <表名>...] [CASCADE];

-- 级联删除多个表
DROP TABLE IF EXISTS users, orders CASCADE;

单行写法：清空表数据 TRUNCATE [TABLE] <表名>[, <表名>...] [RESTART IDENTITY] [CASCADE];

-- 清空表并重置自增序列
TRUNCATE TABLE users RESTART IDENTITY CASCADE;

单行写法：清空并级联 TRUNCATE <表1>, <表2> CASCADE;

-- 同时清空有外键关联的表
TRUNCATE users, orders CASCADE;

视图#

换行写法：创建视图 CREATE [OR REPLACE] VIEW <视图名> AS <SELECT 语句>;

-- 创建或替换视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email FROM users WHERE status = 1;

换行写法：创建物化视图 CREATE MATERIALIZED VIEW <视图名> AS <SELECT 语句> [WITH DATA | WITH NO DATA];

-- 创建物化视图缓存结果
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;

单行写法：刷新物化视图 REFRESH MATERIALIZED VIEW [CONCURRENTLY] <视图名>;

-- 并发刷新物化视图不阻塞查询
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

单行写法：删除视图 DROP VIEW [IF EXISTS] <视图名> [CASCADE];

-- 删除视图
DROP VIEW IF EXISTS active_users;

单行写法：删除物化视图 DROP MATERIALIZED VIEW [IF EXISTS] <视图名>;

-- 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS user_stats;

序列#

单行写法：创建序列 CREATE SEQUENCE [IF NOT EXISTS] <序列名> [START WITH <起始>] [INCREMENT BY <步长>] [MINVALUE <最小>] [MAXVALUE <最大>] [CACHE <缓存>] [CYCLE | NO CYCLE];

-- 创建自定义序列
CREATE SEQUENCE seq_order_no START 1000 INCREMENT 1 CACHE 10;

单行写法：获取下一个值 SELECT nextval('<序列名>');

-- 获取序列下一个值
SELECT nextval('seq_order_no');

单行写法：查看当前值 SELECT currval('<序列名>');

-- 查看当前会话最近获取的值
SELECT currval('seq_order_no');

单行写法：查看最后值 SELECT last_value FROM <序列名>;

-- 查看序列当前最后值
SELECT last_value FROM seq_order_no;

单行写法：重置序列 ALTER SEQUENCE <序列名> RESTART WITH <值>;

-- 重置序列从指定值开始
ALTER SEQUENCE seq_order_no RESTART WITH 1;

单行写法：删除序列 DROP SEQUENCE [IF EXISTS] <序列名>;

-- 删除序列
DROP SEQUENCE IF EXISTS seq_order_no;