在 psql 中,\l 列出数据库,\c dbname 连接到某个数据库,\dt 列出当前数据库中的表。在 SQL 或其他任何客户端中,则查询系统目录:数据库查 pg_database,表查 information_schema.tables。
结果中两张表的类型是 BASE TABLE,视图的类型是 VIEW,全都在 public 模式(schema)中。WHERE 条件过滤掉了系统目录,否则会多出几百行。加上 AND table_type = 'BASE TABLE' 就能排除视图。
查看数据库和表的 psql 命令
这些是 psql 的元命令。psql 会把每条元命令转成一条系统目录查询,所以它们在其他客户端或应用代码中不能用。
| 命令 | 显示 |
|---|---|
\l | 数据库(\l+ 会加上大小) |
\c shop | 连接到数据库 shop |
\conninfo | 当前所在的数据库、用户和主机 |
\dn | schema |
\dt | search_path 上各 schema 中的表 |
\dt sales.* | schema sales 中的表 |
\dt *.* | 所有 schema 中的表,包括系统 schema |
\dt+ | 表及其大小和描述 |
\dv, \di, \ds | 视图、索引、序列 |
\d orders | orders 的列、索引和约束 |
\d+ orders | 同上,再加上存储信息和注释 |
\du | 角色 |
在 shell 中,psql -l 打印数据库列表后退出,-c 执行一条命令:
psql -l
psql -d shop -c '\dt'
用 -E 启动 psql,可以打印出每条元命令背后的 SQL。这是熟悉系统目录表的快捷方法。
用 SQL 列出数据库
服务器上的每个数据库都是 pg_database 中的一行。template0 和 template1 是 CREATE DATABASE 复制用的模板,所以要把它们过滤掉:
在这个页面上,每次运行都会得到一个名字以 lesson_ 开头的全新数据库。结果中会显示它和 postgres,后者是每台服务器都会创建的默认数据库。SELECT current_database() 返回你当前连接的数据库。
查看表的列
在 psql 中,\d orders 会显示表结构。在 SQL 中,information_schema.columns 为每一列保存一行:
id 的 is_nullable 显示 NO,因为主键总是 NOT NULL。它的 column_default 为空:标识列的值来自序列,而不是默认值。
直接查询 pg_catalog
information_schema 是 SQL 标准定义的视图,只显示你拥有某种权限的对象。pg_catalog 中 PostgreSQL 特有的系统目录包含更多信息,比如索引定义:
结果列出三个索引:来自 UNIQUE 的 orders_email_key,来自主键的 orders_pkey,以及你创建的部分索引。每个索引都附带能重建它的 CREATE INDEX 语句。pg_tables 只列出表;视图和物化视图分别在 pg_views 和 pg_matviews 中。
统计每张表的行数
系统目录不保存精确的行数。pg_class.reltuples 保存的是查询规划器的估计值,由 VACUUM 和 ANALYZE 更新。用它找出大表已经足够准确,-1 表示这张表从未被分析过:
SELECT relname, reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY reltuples DESC;
要得到精确的数字,就运行 SELECT count(*) FROM orders,它会读取整张表。PostgreSQL 手册的 System Catalogs 一章介绍了所有系统目录表。
常见问题
PostgreSQL 有 SHOW TABLES 或 SHOW DATABASES 吗?
没有,那是 MySQL 的命令。在 PostgreSQL 中,SHOW 用来读取服务器设置,所以 SHOW TABLES 会报错“unrecognized configuration parameter”。在 psql 中用 \dt 和 \l,或者查询 information_schema.tables 和 pg_database。
为什么 \dt 显示“Did not find any relations”?
要么你连接的数据库不是存放这些表的那个,要么这些表所在的 schema 不在你的 search_path 上。先用 \conninfo 检查,再试试 \dt *.* 或 \dt schema_name.*。
在 PostgreSQL 中怎么切换数据库?
一个连接只属于一个数据库,SQL 里也没有 USE 语句。在 psql 中运行 \c other_db,它会打开一个新连接;在 shell 中则直接启动 psql -d other_db。
在 PostgreSQL 中怎么查看表的大小?
在 psql 中,\dt+ 会多显示一列大小。在 SQL 中,pg_size_pretty(pg_total_relation_size('orders')) 给出表的大小,包括它的索引和 TOAST 数据。