How to Compare Two Schemas in PostgreSQL

Last updated on September 3rd, 2024 at 05:48 am

PostgreSQL Schema is a collection of database tables, functions, views, constraints. Often database administrators may need to compare two schemas in PostgreSQL to see if they are identical or not. It is very tedious to do this manually since a schema consists of many different things. 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.

1. Compare Two Schemas Using SQL

Here’s the SQL query to compare two schemas. Every PostgreSQL installation contains information_schema system table that contains all information about all databases, tables, views and other aspects. 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.

2. Compare Two Schemas in pgAdmin

pgAdmin is a popular database management tool for PostgreSQL databases. 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.

Conclusion

In this article, we have learnt a couple of simple ways to easily compare two PostgreSQL schemas. They are very useful to quickly determine if two databases are similar or not. You can use any of these methods as per your requirement. Otherwise, it will be very cumbersome to compare two databases. Of course, it is important to remember that even if two database schemas are identical, it is possible that they contain different data.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.