PostgreSQL数据库-使用教程一

1. 查询某个schema中所有表的表名、表拥有者、是否有索引、是否有触发器、是否有规则

select * from pg_tables where schemaname ='schema名'

2. 查询某个schema中所有表的表名

select tablename from pg_tables where schemaname ='schema名' 

3. 查询某个schema中某个表中的所有信息

select * from information_schema.columns where table_schema='schema名' and table_name='表名称 '

4. 查询某个schema中某个表中的所有字段

select column_name from information_schema.columns where table_schema='schema名' and table_name='表名称 '

5. 查询某个schema中视图的所有信息

select * from pg_views where schemaname ='schema名'  

6. 查询某个schema中所有的视图名

select viewname from pg_views where schemaname ='public'  

7. 查询两个数据库中所有的表，将结果进行比较。

执行sql命令得出的结果为list（列表），想要进行比较，就要先转化为set类型。然后再比较这两个数组的交/并/差 集

#  -*- coding:utf-8 -*-
# @Author : Emma
import psycopg2

# 连接两个数据库
conn1 = psycopg2.connect(database = "XXXXX1",
                       user = "postgres",
                       password = "postgres",
                       host = "10.XXX.0.XXX",
                       port = "5432")
print("Opened database successfully")
conn2 = psycopg2.connect(database = "XXXXX2",
                       user = "postgres",
                       password = "postgres",
                       host = "10.XXX.0.XXX",
                       port = "5432")

# 建立游标，用来执行数据库操作
cur1 = conn1.cursor()
cur2 = conn2.cursor()

# 执行sql命令
cur1.execute("select tablename from pg_tables where schemaname ='public' ")
cur2.execute("select tablename from pg_tables where schemaname ='public'")
# 取出全部数据
result1 = cur1.fetchall()
result2 = cur2.fetchall()
# 将list类型转换为set类型
s1=set(result1)
s2=set(result2)

# 输出交/并/差 集
print(s1.intersection(s2))         # 输出交集
print(s1.union(s2))                # 输出并集
print(s1.difference(s2))           # 差集， 在s1中但不在s2中的元素
print(s2.difference(s1))           # 差集， 在s2中但不在s1中的元素

# 关闭游标
cur1.close()
cur2.close()
print("cursor closed")

8. 查询某个schema下的所有sequence， 有以下几种方式，第一种更加详细得区分了用户，第二种就是第一种的简写，第三种是查询所有sequence的详细信息，当然也可以单独查询sequence的某一个参数。

select relname from pg_class where relowner=(select usesysid from pg_user where usename='postgres') and relkind='S' 

select c.relname from pg_class c where c.relkind = 'S'

select * from information_schema.sequences where sequence_schema = 'schema名' 

9. 查询mougeschema下的所有function信息。

SELECT
  pg_proc.proname AS "函数名称",
  pg_type.typname AS "返回值数据类型",
  pg_proc.pronargs AS "参数个数"
FROM
  pg_proc
JOIN pg_type
  ON (pg_proc.prorettype = pg_type.oid)
WHERE
  pg_type.typname != 'void'
  AND pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'public');

  当然也可以只查询函数名：

select 
    pg_proc.proname 
from  
    pg_proc 
join pg_type 
  on (pg_proc.prorettype = pg_type.oid) 
where 
    pg_type.typname != 'void' 
    and pronamespace = (select pg_namespace.oid 
                          from pg_namespace                  
                         where nspname = 'public')

10. 查询某个表的主外键约束的所有信息：

SELECT *
 FROM 
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 WHERE tc.table_name like '表名';

11. 查询某个表的主键约束/外键约束：

SELECT
     *
 FROM 
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 WHERE constraint_type = 'FOREIGN KEY/PRIMARY KEY' AND tc.table_name like '表名';