列出PostgreSQL模式中的表

本文翻译自：List tables in a PostgreSQL schema

When I do a \\dt in psql I only get a listing of tables in the current schema ( public by default). 当我在psql中执行\\dt ，我只会得到当前模式中的表列表（默认情况下为public ）。

How can I get a list of all tables in all schemas or a particular schema? 如何获得所有架构或特定架构中所有表的列表？

---

#1楼

参考：https://stackoom.com/question/13dl2/列出PostgreSQL模式中的表

---

#2楼

You can select the tables from information_schema 您可以从information_schema选择表

SELECT * FROM information_schema.tables 
WHERE table_schema = 'public'

---

#3楼

In all schemas: 在所有架构中：

=> \dt *.*

In a particular schema: 在特定模式中：

=> \dt public.*

It is possible to use regular expressions with some restrictions 可以使用某些限制的正则表达式

\dt (public|s).(s|t)
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | s    | table | cpn
 public | t    | table | cpn
 s      | t    | table | cpn

Advanced users can use regular-expression notations such as character classes, for example [0-9] to match any digit. 高级用户可以使用正则表达式表示法，例如字符类，例如[0-9]来匹配任何数字。 All regular expression special characters work as specified in Section 9.7.3, except for . 除之外，所有正则表达式特殊字符均按9.7.3节中的规定工作. which is taken as a separator as mentioned above, * which is translated to the regular-expression notation .* , ? 如上所述，它被用作分隔符， *转换为正则表达式.* ? which is translated to . 转换为. , and $ which is matched literally. 和$字面匹配。 You can emulate these pattern characters at need by writing ? 您可以通过编写?来模拟这些模式字符? for . 为. , (R+|) for R* , or (R|) for R? ，对于R* (R+|)还是对于R? (R|) R? . 。 $ is not needed as a regular-expression character since the pattern must match the whole name, unlike the usual interpretation of regular expressions (in other words, $ is automatically appended to your pattern). 不需要$作为正则表达式字符，因为该模式必须与整个名称匹配，这与对常规表达式的通常解释不同（换句话说， $自动添加到您的模式）。 Write * at the beginning and/or end if you don't wish the pattern to be anchored. 如果您不希望锚定图案，请在开头和/或结尾处输入* 。 Note that within double quotes, all regular expression special characters lose their special meanings and are matched literally. 请注意，在双引号中，所有正则表达式特殊字符都会失去其特殊含义，并且会在字面上进行匹配。 Also, the regular expression special characters are matched literally in operator name patterns (ie, the argument of \\do ). 同样，正则表达式特殊字符在运算符名称模式（即\\do的参数）中按字面值进行匹配。

---

#4楼

除了information_schema ，还可以使用pg_tables ：

select * from pg_tables where schemaname='public';

---

#5楼

For those coming across this in the future: 对于将来遇到这种情况的人：

If you would like to see a list of relations for several schemas: 如果您想查看几种模式的关系列表：

$psql mydatabase
mydatabase=# SET search_path TO public, usa;   #schema examples
SET
mydatabase=# \dt
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | counties        | table | postgres
 public | spatial_ref_sys | table | postgres
 public | states          | table | postgres
 public | us_cities       | table | postgres
 usa    | census2010      | table | postgres