显示 PostgreSQL 中的表结构和表列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25639088/
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
Show table structure and list of tables in PostgreSQL
提问by Mary Daisy Sanchez
I had employed MySQL for a couple of former projects. But now have decided to switch to PostgreSQL. Not that version 8 also works on that, ahem other OS which I'm stuck with at work.
我曾在几个以前的项目中使用 MySQL。不过现在已经决定改用PostgreSQL了。不是那个版本 8 也适用于那个，嗯，我在工作中坚持使用的其他操作系统。
But alas, two of the most useful commands appear to be missing:
但遗憾的是，似乎缺少两个最有用的命令：
SHOW TABLESDESCRIBE table
SHOW TABLESDESCRIBE table
Inasmuch as my prototyping DB is on my NetBSD server at home while my data waiting to be 'based is at work, such that I have to connect via Perl/DBI and XML-RPC (not psql, alas). The IT dept here just says, "Use MS-Access", so no help there.
因为我的原型数据库在家里的 NetBSD 服务器上，而我的数据正在等待“基于”，因此我必须通过 Perl/DBI 和 XML-RPC（不是 psql，唉）进行连接。这里的 IT 部门只是说“使用 MS-Access”，所以没有帮助。
While I'm in the initial stage I need an informative way to blunder around and see what's what as I try different ways to build this thing. For that I had always relied on the two above from MySQL.
当我处于初始阶段时，我需要一种信息丰富的方式来解决问题，看看当我尝试不同的方法来构建这个东西时会发生什么。为此，我一直依赖于 MySQL 的上述两个。
I can't believe there is no way for PostgreSQL to tell me what the current DB's table structure is via simple SQL queries executed remotely.
我不敢相信 PostgreSQL 无法通过远程执行的简单 SQL 查询告诉我当前数据库的表结构是什么。
Surely there must be. But I can't seem to find out from the couple of books I have. All I dug up was some ultra-lame hack to get column names for an already known table name by doing a "WHERE 1 != 1" or some such so that no actual rows could be returned. Not very informative, that. Surely I've missed the point, somewhere.
肯定有。但是我似乎无法从我拥有的几本书中找到答案。我挖出的只是一些非常蹩脚的 hack，通过执行“WHERE 1 != 1”或类似的操作来获取已知表名的列名，这样就不会返回实际的行。不是很翔实，那个。当然，我在某处错过了重点。
So enlighten me, please. What, pray tell, are the PostgreSQL-ish SQL queries one uses so as to explore a given DB's table structure? What is the PostgreSQL translation for "SHOW TABLES" and "DESCRIBE table"?
所以请赐教。请告诉我，PostgreSQL 风格的 SQL 查询是用来探索给定数据库的表结构的吗？“SHOW TABLES”和“DESCRIBE table”的 PostgreSQL 翻译是什么？
采纳答案by Vivek S.
As per the Documentation
根据文档
SELECT
table_schema || '.' || table_name as show_tables
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND
table_schema NOT IN ('pg_catalog', 'information_schema');
for more convenience make it as a function
为了更方便，把它作为一个函数
create or replace function show_tables() returns SETOF text as $$
SELECT
table_schema || '.' || table_name as show_tables
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND
table_schema NOT IN ('pg_catalog', 'information_schema');
$$
language sql;
So we can get the tablesusing
因此，我们可以得到表使用
select show_tables()
For the table description
对于表格说明
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name ='table_name';
as a Function
作为函数
create or replace function describe_table(tbl_name text) returns table(column_name
varchar, data_type varchar,character_maximum_length int) as $$
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = ;
$$
language 'sql';
select * from describe_table('a_table_name');
回答by Craig Ringer
SHOW TABLESand DESCRIBE TABLEare MySQL-specific admin commands, and nothing to do with standard SQL.
SHOW TABLES并且DESCRIBE TABLE是特定于 MySQL 的管理命令，与标准 SQL 无关。
You want the:
你想要：
\d
and
和
\d+ tablename
commands from psql.
来自psql.
These are implemented client-side. I find this odd myself, and would love to move them server-side as built-in SQL commands one day.
这些是在客户端实现的。我自己觉得这很奇怪，并且希望有一天将它们作为内置 SQL 命令移动到服务器端。
Other clients provide other ways to browse the structure - for example, PgAdmin-III.
其他客户端提供其他方式来浏览结构 - 例如，PgAdmin-III。
If you want a portable way to get table structure in code, you should use the information_schemaviews, which are SQL-standard. See information_schema. They're available in MySQL, PostgreSQL, Ms-SQL, and most other DBs. The downside is that they're fiddlier to use, so they aren't convenient for quick access when you're just browsing a DB structure.
如果您想要一种在代码中获取表结构的可移植方式，您应该使用information_schemaSQL 标准的视图。见information_schema。它们在 MySQL、PostgreSQL、Ms-SQL 和大多数其他数据库中可用。缺点是它们使用起来更加繁琐，因此当您只是浏览数据库结构时，它们不便于快速访问。
