postgresql psql 列出所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12445608/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Psql list all tables
提问by pethel
I would like to list all tables in the liferaydatabase in my PostgreSQL install. How do I do that?
我想liferay在我的 PostgreSQL 安装中列出数据库中的所有表。我怎么做？
I would like to execute SELECT * FROM applications;in the liferaydatabase. applicationsis a table in my liferay db. How is this done?
我想SELECT * FROM applications;在liferay数据库中执行。applications是我的 liferay 数据库中的一张表。这是怎么做的？
Here's a list of all my databases:
这是我所有数据库的列表：
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
liferay | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | liferay=CTc/postgres
lportal | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres=#
回答by Craig Ringer
If you wish to list alltables, you must use:
如果要列出所有表，必须使用：
\dt *.*
to indicate that you want all tables in all schemas. This will include tables in pg_catalog, the system tables, and those in information_schema. There's no built-in way to say "all tables in all user-defined schemas"; you can, however, set your search_pathto a list of all schemas of interest before running \dt.
表示您想要所有模式中的所有表。这将包括 中的表pg_catalog、系统表和information_schema. 没有内置的方法可以说“所有用户定义模式中的所有表”；但是，您可以search_path在运行之前将您设置为所有感兴趣的模式的列表\dt。
You may want to do this programmatically, in which case psqlbackslash-commands won't do the job. This is where the INFORMATION_SCHEMAcomes to the rescue. To list tables:
您可能希望以编程方式执行此操作，在这种情况下，psql反斜杠命令将无法完成这项工作。这是该INFORMATION_SCHEMA来救援。列出表格：
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
BTW, if you ever want to see what psqlis doing in response to a backslash command, run psqlwith the -Eflag. eg:
顺便说一句，如果您想查看psql响应反斜杠命令的操作，请psql使用该-E标志运行。例如：
$ psql -E regress
regress=# \list
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
so you can see that psqlis searching pg_catalog.pg_databasewhen it gets a list of databases. Similarly, for tables within a given database:
所以你可以看到它在获取数据库列表时psql正在搜索pg_catalog.pg_database。同样，对于给定数据库中的表：
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
It's preferable to use the SQL-standard, portable INFORMATION_SCHEMAinstead of the Pg system catalogs where possible, but sometimes you need Pg-specific information. In those cases it's fine to query the system catalogsdirectly, and psql -Ecan be a helpful guide for how to do so.
INFORMATION_SCHEMA如果可能，最好使用 SQL 标准的、可移植的而不是 Pg 系统目录，但有时您需要特定于 Pg 的信息。在这些情况下，直接查询系统目录是可以的，并且psql -E可以成为有关如何执行此操作的有用指南。
回答by d11wtq
Connect to the database, then list the tables:
连接到数据库，然后列出表：
\c liferay
\dt
That's how I do it anyway.
反正我就是这样做的。
You can combine those two commands onto a single line, if you prefer:
如果您愿意，可以将这两个命令组合到一行中：
\c liferay \dt
回答by nikkypx
To see the public tables you can do
要查看您可以执行的公共表
list tables
列出表格
\dt
list table, view, and access privileges
列出表、查看和访问权限
\dp or \z
or just the table names
或只是表名
select table_name from information_schema.tables where table_schema = 'public';
回答by Sevki Kocadag
In SQL Query, you can write this code:
在 SQL Query 中，您可以编写以下代码：
select table_name from information_schema.tables where table_schema='YOUR_TABLE_SCHEME';
Replace your table scheme with YOUR_TABLE_SCHEME;
用 YOUR_TABLE_SCHEME 替换您的表方案；
Example:
例子：
select table_name from information_schema.tables where table_schema='eLearningProject';
To see all scheme and all tables, there is no need of where clause:
要查看所有方案和所有表，不需要 where 子句：
select table_name from information_schema.tables
回答by mcolak
A one-line example is
一个单行的例子是
\dt schemaname.*
in your senario
在你的情景中
\dt public.*
回答by danbst
This can be used in automation scripts if you don't need all tables in all schemas:
如果您不需要所有模式中的所有表，这可以用于自动化脚本：
for table in $(psql -qAntc '\dt' | cut -d\| -f2); do
...
done
回答by Sunil Kumar
You can type \?to get information on all the commands supported in psql.
您可以键入\?以获取有关 psql 支持的所有命令的信息。
