Is there any direct method to compare tables between MySQL and PostgreSQL.Comparing should include field name, types, default values, etc; As an example below,
Below is a dump of PostgreSQL
| column_name | data_type | is_nullable |
|---|---|---|
| service_id | integer | NO |
| service_name | character varying | YES |
| service_code | character varying | YES |
| service_short_code | character varying | YES |
Below is a dump of MySQL
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| service_id | int(11) | NO | PRI | NULL | auto_increment |
| service_name | varchar(50) | NO | UNI | NULL | |
| service_code | varchar(50) | NO | NULL | ||
| service_desc | varchar(150) | NO | NULL |
from above example, need to get an output for in MySQL schema is missing'service_short_code' field and also it's type and if it's nullable, also 'service_desc' field not in PostgreSQL table and it's other data.
Will this be possible to do one time or any other way to do this other than copy and pasting this type of dumps in to excel file and mapping manually.
1 Answer 1
The only way I know of how to do this in a more automated way is by using a comparison tool, such as SQL Examiner. Note, I'm not affiliated with this tool in any way other than being a satisfied user of it myself.
SQL Examiner compares the schema between two database systems (rather seamlessly), and can compare across database systems including MySQL and PostgreSQL:
SQL Examiner works equally well with all versions and editions of Microsoft SQL Server, PostgreSQL, Oracle and MySQL. Azure SQL Database, SQL Server LocalDB, and SQL Compact are also supported.
With the Professional edition of the tool, you can even copy database schemas and schema changes between different database systems...
They also offer a data comparison tool too.
ORDER BYwhen extracting, so that they will be in the "same" order.SHOW CREATE TAB:LE; does Postgres have something similar?SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'name';