• 查询pgsql数据库(及其大小)

select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

以本地postgres库为例
结果：[(‘postgres’, ‘7723 kB’), (‘test’, ‘7683 kB’), (‘template1’, ‘7521 kB’), (‘template0’, ‘7521 kB’)]

• 查询pgsql数据库下有哪些模式

SELECT * FROM information_schema.schemata;

Ps：
选择的是postgres数据库

conn = psycopg2.connect(database="postgres", user="postgres", password="*", host="localhost", port="5432")

返回：[(‘postgres’, ‘information_schema’, ‘postgres’, None, None, None, None), (‘postgres’, ‘public’, ‘postgres’, None, None, None, None), (‘postgres’, ‘pg_catalog’, ‘postgres’, None, None, None, None), (‘postgres’, ‘pg_toast_temp_1’, ‘postgres’, None, None, None, None), (‘postgres’, ‘pg_temp_1’, ‘postgres’, None, None, None, None), (‘postgres’, ‘pg_toast’, ‘postgres’, None, None, None, None)]

黑色加粗为postgres库下的模式

• 查询pgsql模式下有哪些表

select tablename from pg_tables where schemaname='public';  --这里是public模式

返回：[(‘titles_test’,), (‘ctrip_hotel_order’,), (‘testtest’,)]

查看pgsql视图下有哪些表

SELECT   viewname   FROM   pg_views  WHERE     schemaname ='public'  ;

• 查询pgsql表的信息
  
• 查询列名

select * from information_schema.columns where table_schema='public' and table_name='titles_test';

• 查看pgsql表的注释

select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' and relname='表名';

select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname; --所有表的

• 获取pgsql表的字段名、类型、注释、是否为空:（可以查视图的）

SELECT a.attname,col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull FROM pg_class as c,pg_attribute as a where c.relname = 'zhpjzb' and a.attrelid = c.oid and a.attnum>0;  

ps：
(select oid from pg_class where relname = ‘表名’;

select * from pg_attribute where attrelid = ‘29279856’; )

表名的oid和pg_attribute里面的attrelid关联

• 查看前几行

select * from 表名 limit 10;

参考资料：
https://www.cnblogs.com/ylq1990/p/10059219.html