- 查询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;