psql 列出所有表 [英] Psql list all tables

问题描述

我想在我的 PostgreSQL 安装中列出 liferay 数据库中的所有表.我该怎么做?

I would like to list all tables in the liferay database in my PostgreSQL install. How do I do that?

我想在 liferay 数据库中执行 SELECT * FROM applications;.applications 是我的 liferay 数据库中的一个表.这是怎么做的?

I would like to execute SELECT * FROM applications; in the liferay database. applications is a table in my liferay db. How is this done?

这是我所有数据库的列表:

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=# 

推荐答案

如果您希望列出所有表，您必须使用:

If you wish to list all tables, you must use:

\dt *.*

表示您想要所有模式中的所有表.这将包括pg_catalog 中的表、系统表和information_schema 中的表.没有内置方法可以说所有用户定义模式中的所有表"；但是，您可以在运行 \dt 之前将 search_path 设置为所有感兴趣模式的列表.

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_path to a list of all schemas of interest before running \dt.

您可能希望以编程方式执行此操作，在这种情况下，psql 反斜杠命令将无法完成这项工作.这就是 INFORMATION_SCHEMA 的来源到救援.列出表格:

You may want to do this programmatically, in which case psql backslash-commands won't do the job. This is where the INFORMATION_SCHEMA comes to the rescue. To list tables:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

顺便说一句，如果您想查看 psql 在响应反斜杠命令时正在做什么，请运行带有 -E 标志的 psql.例如:

BTW, if you ever want to see what psql is doing in response to a backslash command, run psql with the -E flag. eg:

$ 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;
**************************

所以您可以看到 psql 在获取数据库列表时正在搜索 pg_catalog.pg_database.同样，对于给定数据库中的表:

so you can see that psql is searching pg_catalog.pg_database when it gets a list of databases. Similarly, for tables within a given 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;

如果可能，最好使用 SQL 标准的、可移植的 INFORMATION_SCHEMA 而不是 Pg 系统目录，但有时您需要特定于 Pg 的信息.在这些情况下，可以直接查询系统目录，psql -E 可以作为如何执行此操作的有用指南.

It's preferable to use the SQL-standard, portable INFORMATION_SCHEMA instead of the Pg system catalogs where possible, but sometimes you need Pg-specific information. In those cases it's fine to query the system catalogs directly, and psql -E can be a helpful guide for how to do so.

这篇关于psql 列出所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！