1

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.

4
  • Probably a better fit on Database Administrators Nov 20, 2023 at 2:06
  • Export both as CSV files, then compare the files. Be sure to use an ORDER BY when extracting, so that they will be in the "same" order.
    – Rick James
    Nov 20, 2023 at 16:30
  • MySQL has SHOW CREATE TAB:LE; does Postgres have something similar?
    – Rick James
    Nov 21, 2023 at 18:13
  • Postgres has a query somewhat equal to desc table_name in MySQL though. It's like below, SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'name';
    – chinthakaS
    Nov 24, 2023 at 6:25

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.