在 PostgreSQL 中显示表

Show tables in PostgreSQL

提问by flybywire

What's the equivalent to show tables(from MySQL) in PostgreSQL?

show tablesPostgreSQL 中（来自 MySQL）的等价物是什么？

回答by Mihai Limb??an

From the psqlcommand line interface,

从psql命令行界面，

First, choose your database

首先，选择你的数据库

\c database_name

Then, this shows all tables in the current schema:

然后，这将显示当前架构中的所有表：

\dt

---

---

Programmatically (or from the psqlinterface too, of course):

以编程方式（psql当然也可以从界面中）：

SELECT * FROM pg_catalog.pg_tables;

The system tables live in the pg_catalogdatabase.

系统表存在于pg_catalog数据库中。

回答by JLarky

Login as superuser:

以超级用户身份登录：

sudo -u postgres psql

You can list all databases and users by \lcommand, (list other commands by \?).

您可以通过\l命令列出所有数据库和用户，（通过 列出其他命令\?）。

Now if you want to see other databases you can change user/database by \ccommand like \c template1, \c postgres postgresand use \d, \dtor \dSto see tables/views/etc.

现在，如果您想查看其他数据库，您可以通过\c诸如\c template1、\c postgres postgres和 use 之类的命令更改用户/数据库\d，\dt或者\dS查看表/视图/等。

回答by Milen A. Radev

(For completeness)

（为了完整性）

You could also query the (SQL-standard) information schema:

您还可以查询（SQL 标准）信息架构：

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

回答by Yuci

You can use PostgreSQL's interactive terminal Psql to show tables in PostgreSQL.

您可以使用 PostgreSQL 的交互式终端 Psql 来显示 PostgreSQL 中的表。

1. Start Psql

1.启动psql

Usually you can run the following command to enter into psql:

通常可以运行如下命令进入psql：

psql DBNAME USERNAME

For example, psql template1 postgres

例如， psql template1 postgres

One situation you might have is: suppose you login as root, and you don't remember the database name. You can just enter first into Psql by running:

您可能遇到的一种情况是：假设您以 root 身份登录，但不记得数据库名称。您可以通过运行以下命令首先进入 Psql：

sudo -u postgres psql

In some systems, sudo command is not available, you can instead run either command below:

在某些系统中， sudo 命令不可用，您可以运行以下任一命令：

psql -U postgres
psql --username=postgres

2. Show tables

2. 显示表格

Now in Psql you could run commands such as:

现在在 Psql 中，您可以运行以下命令：

1. \?list all the commands
2. \llist databases
3. \conninfodisplay information about current connection
4. \c [DBNAME]connect to new database, e.g., \c template1
5. \dtlist tables of the public schema
6. \dt <schema-name>.*list tables of certain schema, e.g., \dt public.*
7. \dt *.*list tables of all schemas
8. Then you can run SQL statements, e.g., SELECT * FROM my_table;(Note: a statement must be terminated with semicolon ;)
9. \qquit psql

1. \?列出所有命令
2. \l列出数据库
3. \conninfo显示有关当前连接的信息
4. \c [DBNAME]连接到新数据库，例如， \c template1
5. \dt列出公共模式的表
6. \dt <schema-name>.*列出某些模式的表，例如， \dt public.*
7. \dt *.*列出所有模式的表
8. 然后就可以运行SQL语句了，例如，SELECT * FROM my_table;（注意：语句必须以分号结束;）
9. \q退出 psql

回答by nish

1. First login as postgres user:

   sudo su - postgres

2. connect to the required db: psql -d databaseName

3. \dtwould return the list of all table in the database you're connected to.

1. 首次以 postgres 用户身份登录：

   sudo su - postgres

2. 连接到所需的数据库： psql -d databaseName

3. \dt将返回您连接到的数据库中所有表的列表。

回答by bsb

Running psql with the -E flag will echo the query used internally to implement \dt and similar:

使用 -E 标志运行 psql 将回显内部用于实现 \dt 和类似的查询：

sudo -u postgres psql -E

postgres=# \dt       
********* QUERY **********
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' 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;        
**************************

回答by Shashishekhar Hasabnis

Login as a superuser so that you can check all the databases and their schemas:-

以超级用户身份登录，以便您可以检查所有数据库及其架构：-

sudo su - postgres

Then we can get to postgresql shell by using following command:-

然后我们可以使用以下命令进入 postgresql shell：-

psql

You can now check all the databases list by using the following command:-

您现在可以使用以下命令检查所有数据库列表：-

\l

If you would like to check the sizes of the databases as well use:-

如果您还想检查数据库的大小，请使用：-

\l+

Press qto go back.

按q返回。

Once you have found your database now you can connect to that database using the following command:-

现在找到数据库后，您可以使用以下命令连接到该数据库：-

\c database_name

Once connected you can check the database tables or schema by:-

连接后，您可以通过以下方式检查数据库表或架构：-

\d

Now to return back to the shell use:-

现在返回到 shell 使用：-

q

Now to further see the details of a certain table use:-

现在要进一步查看某个表的详细信息，请使用：-

\d table_name

To go back to postgresql_shell press \q.

要返回 postgresql_shell，请按\q。

And to return back to terminal press exit.

并返回到终端按exit。

回答by Banned_User

If you only want to see the list of tables you've created, you may only say:

如果您只想查看您创建的表的列表，您可以只说：

\dt

\dt

But we also have PATTERNwhich will help you customize which tables to show. To show all including pg_catalogSchema, you can add *.

但我们也有PATTERN这将帮助您自定义要显示的表格。要显示所有包括pg_catalog架构，您可以添加*.

\dt *

\dt *

If you do: \?

如果你这样做： \?

\dt[S+] [PATTERN] list tables

\dt[S+] [PATTERN] 列出表格

回答by Aryan

use only see a tables

使用只看一张表

=> \dt

if want to see schema tables

如果想查看模式表

=>\dt+

if you want to see specific schema tables

如果您想查看特定的架构表

=>\dt schema_name.* 

回答by J4cK

First Connect with the Database using following command

首先使用以下命令连接数据库

\c database_name

And you will see this message - You are now connected to database database_name. And them run the following command

您将看到此消息 - You are now connected to database database_name。他们运行以下命令

SELECT * FROM table_name;

In database_name and table_name just update with your database and table name

在 database_name 和 table_name 中，只需更新您的数据库和表名