Sometimes you may need to compare two schemas in PostgreSQL. You can use a SQL query to accomplish this, or use one of the many third-party tools that allow you to compare two schemas in PostgreSQL. In this article we will look at both ways to compare two schemas in PostgreSQL.
How to Compare Two Schemas in PostgreSQL
We will look at two ways to compare two schemas in PostgreSQL – using SQL queries and using pgAdmin.
Compare Two Schemas Using SQL
Here’s the SQL query to compare two schemas. Replace schema1 and schema2 with the names of two schemas you want to compare.
select COALESCE(c1.table_name, c2.table_name) as table_name,
COALESCE(c1.column_name, c2.column_name) as table_column,
c1.column_name as schema1,
c2.column_name as schema2
from
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = 'schema1') c1
full join
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = 'schema2') c2
on c1.table_name = c2.table_name and c1.column_name = c2.column_name
where c1.column_name is null
or c2.column_name is null
order by table_name,
table_column;
The above query basically lists all rows present in either schema with information about its presence/absence in the other schema.
- table_name – name of table present in schema1 or schema2
- table_column – name of column present in schema1 or schema2
- schema1 – if column exists in schema1 its name is displayed, else it is null.
- schema2 – if column exists in schema2 its name is displayed, else it is null.
Compare Two Schemas in pgAdmin
We will use the Schema Diff feature of pgAdmin to compare schemas or databases or other objects.
Schema diff tool allows you to trace the difference between two database objects, and list the different SQL statements to synchronize databases.
However, please note, the source and target servers should be of same major server version.
Here are the steps to compare two schemas using Schema Diff tool.
1. Click Schema Diff option, under Tools menu.
2. You will see a form where you need to select Source and Target
3. Select server versions, source and target servers, and database/schema depending on your requirement.
4. Click Compare to compare two databases/schemas. You will see a detailed list of object comparison result, followed by DDL comparison result
5. The object comparison result is presented as a tree, which you can click to expand/collapse.
6. DDL comparison results are presented in 3 panels. In the left panel you will find SQL queries of source schema. In the middle panel, you have the SQL statements for target schema. And in the right panel, you will find the difference between SQL statements of two schemas.
7. You will find many options to refine comparison results such as Query Editor, Script generator and filters.
pgAdmin Schema diff is much more comprehensive than SQL-based schema comparison, and is highly recommended. Hopefully, this article will help you compare two schemas in PostgreSQL.
Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.
