在PostgreSQL中显示表的方法

技术背景

在PostgreSQL数据库管理中，查看数据库中的表是一项基础且常见的操作。无论是开发人员进行数据查询、测试，还是数据库管理员进行日常维护，都需要能够方便快捷地查看表信息。PostgreSQL提供了多种方式来显示表，包括使用命令行工具psql和执行SQL查询语句。

实现步骤

使用psql命令行界面

1. 选择数据库：首先连接到指定数据库，使用\c database_name命令，例如\c template1。
2. 显示表：
   • \dt：显示当前模式（通常是public）下的所有表。
   • \dt <schema-name>.*：显示特定模式下的所有表，例如\dt public.*。
   • \dt *.*：显示所有模式下的所有表。

编程方式或在psql界面执行SQL查询

• SELECT * FROM pg_catalog.pg_tables;：查询系统表pg_catalog.pg_tables，显示所有表信息。
• 查询信息模式：

SELECT
    table_schema || '.' || table_name
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

使用psql的详细步骤

1. 启动psql：
   • 通常使用psql DBNAME USERNAME命令，例如psql template1 postgres。
   • 若以root用户登录且忘记数据库名，可使用sudo -u postgres psql。
   • 在某些系统中，可使用psql -U postgres或psql --username=postgres。
2. 常用命令：
   • \?：列出所有命令。
   • \l：列出所有数据库。
   • \conninfo：显示当前连接信息。
   • \c [DBNAME]：连接到新数据库。
   • \dt：列出公共模式的表。
   • \dt <schema-name>.*：列出特定模式的表。
   • \dt *.*：列出所有模式的表。
   • \q：退出psql。

使用psql查看表详细信息

• \d table_name：查看特定表的详细信息，包括列、类型、引用和键约束。

使用psql的-E标志

运行psql -E可以查看\dt等命令内部执行的查询语句。

在pgAdmin4中显示表

在pgAdmin4中，可使用以下SQL查询显示表：

select * from information_schema.tables where table_schema='public';

核心代码

查询系统表pg_catalog.pg_tables

SELECT * FROM pg_catalog.pg_tables;

查询信息模式

SELECT
    table_schema || '.' || table_name
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

查询公共模式下的表名

select table_name from information_schema.tables where table_schema='public';

最佳实践

• 使用特定模式查询：如果数据库中有多个模式，使用\dt <schema-name>.*可以更精确地查看特定模式下的表。
• 结合-E标志：在调试或学习时，使用psql -E可以了解psql命令背后的SQL查询逻辑。
• 使用\d查看表详细信息：当需要了解表的结构时，使用\d table_name可以获取详细的列信息。

常见问题

\dt只显示公共模式的表

如果需要查看其他模式的表，应使用\dt <schema-name>.*指定模式。

忘记数据库名或用户名

可以先以超级用户身份登录，如sudo -u postgres psql，然后使用\l列出所有数据库，再使用\c连接到指定数据库。

命令行执行psql报错

确保psql已正确安装，并且提供的数据库名、用户名、端口和服务器地址正确。