Menu

PostgreSQL 查看所有数据库和表(psql 与 SQL)

在 psql 中,\l 列出数据库,\c 切换数据库,\dt 列出其中的表。在 SQL 中,可以查询 pg_database、information_schema.tables 和 information_schema.columns。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

在 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当前所在的数据库、用户和主机
\dnschema
\dtsearch_path 上各 schema 中的表
\dt sales.*schema sales 中的表
\dt *.*所有 schema 中的表,包括系统 schema
\dt+表及其大小和描述
\dv, \di, \ds视图、索引、序列
\d ordersorders 的列、索引和约束
\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 数据。

Coddy 编程语言插图

和 Coddy 一起学编程

开始