Differences in column types between database schemas in PostgreSQL
The following approach can be used to find the differences in tables and views column structure as well as in column types between database schemas similar to each other.
Plan
- Find an ordered list of tables/views, their columns with types in one database schema
- Find an ordered list of tables/views, their columns with types in another database schema similar to the one mentioned above
- Compare these two lists using own favorite method
Metadata retrieval from database schemas
An ordered list of tables/views with column types can be selected using the query below.
For the subsequent proper usage during comparison the query includes:
- Additional padding with spaces in names of tables, views and their columns - to have equal width of respective columns in SQL results from different schemas.
- Additional collate option for sorting - to guarantee the same ordering in SQL results of the data from different schemas, which can reside in different databases with different settings affecting natural ordering behavior.
select
format('%-50s', table_name) table_name_,
format('%-50s', column_name) column_name_,
is_nullable,
data_type,
character_maximum_length,
character_octet_length,
numeric_precision,
numeric_precision_radix,
numeric_scale,
datetime_precision,
format('%-50s', column_default) column_default_
from
information_schema.columns
where
table_schema = 'information_schema'
order by
table_name collate "C",
column_name collate "C"
;
Ensure the necessary filtering in where clause is applied.
SQL results will look like:
table_name_ |column_name_ |is_nullable |data_type |character_maximum_length |character_octet_length |numeric_precision |numeric_precision_radix |numeric_scale |datetime_precision |column_default_ |
---------------------------------------------------|---------------------------------------------------|------------|------------------|-------------------------|-----------------------|------------------|------------------------|--------------|-------------------|---------------------------------------------------|
_pg_foreign_data_wrappers |authorization_identifier |YES |character varying | |1073741824 | | | | | |
_pg_foreign_data_wrappers |fdwoptions |YES |ARRAY | | | | | | | |
_pg_foreign_data_wrappers |fdwowner |YES |oid | | | | | | | |
_pg_foreign_data_wrappers |foreign_data_wrapper_catalog |YES |character varying | |1073741824 | | | | | |
_pg_foreign_data_wrappers |foreign_data_wrapper_language |YES |character varying | |1073741824 | | | | | |
_pg_foreign_data_wrappers |foreign_data_wrapper_name |YES |character varying | |1073741824 | | | | | |
_pg_foreign_data_wrappers |oid |YES |oid | | | | | | | |
_pg_foreign_servers |authorization_identifier |YES |character varying | |1073741824 | | | | | |
_pg_foreign_servers |foreign_data_wrapper_catalog |YES |character varying | |1073741824 | | | | | |
_pg_foreign_servers |foreign_data_wrapper_name |YES |character varying | |1073741824 | | | | | |
_pg_foreign_servers |foreign_server_catalog |YES |character varying | |1073741824 | | | | | |
Comparison
Once the lists are generated, they can be compared in the text mode using own favorite tool.
It can be diff linux command or some graphical tool like WinMerge or Beyond Compare.