PostgreSQL database - Tutorial 1

1. Query the table name, owner, index, trigger and rule of all tables in a schema

select * from pg_tables where schemaname ='schema name'

2. Query the table names of all tables in a schema

select tablename from pg_tables where schemaname ='schema name' 

3. Query all information in a table in a schema

select * from information_schema.columns where table_schema='schema name' and table_name='Table name '

4. Query all fields in a table in a schema

select column_name from information_schema.columns where table_schema='schema name' and table_name='Table name '

5. Query all information of views in a schema

select * from pg_views where schemaname ='schema name'  

6. Query all view names in a schema

select viewname from pg_views where schemaname ='public'  

7. Query all tables in the two databases and compare the results.

The result of executing sql command is list. If you want to compare, you need to convert to set type first. Then compare the intersection / Union / difference sets of the two arrays

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

# Connect two databases
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")

# Create a cursor to perform database operations
cur1 = conn1.cursor()
cur2 = conn2.cursor()

# Execute sql command
cur1.execute("select tablename from pg_tables where schemaname ='public' ")
cur2.execute("select tablename from pg_tables where schemaname ='public'")
# Take out all data
result1 = cur1.fetchall()
result2 = cur2.fetchall()
# Convert list type to set type
s1=set(result1)
s2=set(result2)

# Output intersection / Union / difference set
print(s1.intersection(s2))         # Output intersection
print(s1.union(s2))                # Output Union
print(s1.difference(s2))           # Difference set, elements not in s2 in s1
print(s2.difference(s1))           # Difference set, elements not in s1 but in s2

# Close cursor
cur1.close()
cur2.close()
print("cursor closed")

 

Tags: Database SQL

Posted on Sun, 09 Feb 2020 10:51:31 -0800 by solon