postgres

最新推荐文章于 2023-05-29 11:14:52 发布

转载最新推荐文章于 2023-05-29 11:14:52 发布 · 91 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/1186013/blog/1477079

文章标签：

#数据库 #python

本文介绍了在MySQL和PostgreSQL中如何使用不同的命令来查看数据库、表及列的信息，包括SHOW TABLES、d等命令的具体用法。

2019独角兽企业重金招聘Python工程师标准>>>

mysql: SHOW TABLES
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

mysql: SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;

mysql: SHOW COLUMNS
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

mysql: DESCRIBE TABLE
postgresql: \d+ table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

\list or \l: list all databases
\dt: list all tables in the current database

SELECT * FROM pg_catalog.pg_tables

(For completeness)

You could also query the (SQL-standard) information schema:
SELECT
    table_schema || '.' || table_name
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

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

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

转载于:https://my.oschina.net/u/1186013/blog/1477079