PostgreSQL 常用命令

🐘 PostgreSQL 常用命令参考手册

📥 连接数据库

psql -h 主机名 -p 端口 -U 用户名 -d 数据库名

示例：

psql -h localhost -p 5432 -U postgres -d mydatabase

本地简写方式：

psql -U postgres -d mydatabase

---

🔁 数据库切换

\c 数据库名
-- 或
\connect 数据库名

带用户名和主机：

\c 数据库名 用户名 主机

---

🛠️ 数据库操作

创建数据库

CREATE DATABASE 数据库名;

删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

---

📄 表操作

查看所有表

\dt          -- 当前schema下的表
\dt *.*      -- 所有schema下的表

使用 SQL 查询表名：

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

创建表

CREATE TABLE 表名 (
  列名1 数据类型 [约束],
  列名2 数据类型 [约束],
  ...
);

删除表

DROP TABLE [IF EXISTS] 表名;

查看表结构

\d 表名

---

🧱 列操作

添加列

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [约束];

删除列

ALTER TABLE 表名 DROP COLUMN 列名;

修改列数据类型

ALTER TABLE 表名 ALTER COLUMN 列名 TYPE 新数据类型;

重命名列

ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;

---

🔍 查询表数据

查询所有行

SELECT * FROM 表名;

限制返回行数

SELECT * FROM 表名 LIMIT 10;

条件查询

SELECT * FROM 表名 WHERE 列名 = '值';

排序查询

SELECT * FROM 表名 ORDER BY 列名 [ASC|DESC];

查询指定列

SELECT 列名1, 列名2 FROM 表名;

---

🧹 清空表数据

删除所有数据但保留结构

TRUNCATE TABLE 表名;
-- 或
DELETE FROM 表名;

同时清空多个表

TRUNCATE TABLE 表1, 表2, 表3 [CASCADE];

重置自增 ID

TRUNCATE TABLE 表名 RESTART IDENTITY;

---

👥 实际使用示例

创建用户表

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

添加列

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

删除列

ALTER TABLE users DROP COLUMN phone;

清空并重置 ID

TRUNCATE TABLE users RESTART IDENTITY;

---

💾 数据库备份与还原

备份整个数据库

使用 pg_dump 命令：

pg_dump -U 用户名 -h 主机名 -p 端口 -F c -b -v -f 路径/备份文件名.backup 数据库名

示例：

pg_dump -U postgres -F c -b -v -f /backup/mydb.backup mydatabase

说明：

• -F c: 使用自定义格式（推荐）
• -b: 包含 large object
• -v: 显示详细信息
• -f: 指定输出文件路径

---

还原数据库

使用 pg_restore 命令：

pg_restore -U 用户名 -h 主机名 -p 端口 -d 数据库名 -v 路径/备份文件名.backup

示例：

pg_restore -U postgres -d mydatabase -v /backup/mydb.backup

若目标数据库不存在，请先创建：

createdb -U postgres mydatabase

---

备份为 SQL 文件（可读可编辑）

pg_dump -U postgres -F p -f /backup/mydb.sql mydatabase

使用 SQL 文件还原

psql -U postgres -d mydatabase -f /backup/mydb.sql